asked
Update query - 'Subquery returned more than 1 value'
Hi there, I'm wondering if anyone may be able to assist with the following query. I'm attempting to update a single value on a table for a number of rows. The select query returns the data I desire and takes the form... select * from tbConditionComponent where SurveyUID in ('4DEC2241-43D8-DE11-B935-0017A4770806', '33536B73-43DB-DE11-B935-0017A4770806' etc... This returns all the rows I would like to update. Within the table there is a column called Status which I wish to change from 0 to 1 on all selected rows. So I've changed the above to a update statement and added the Status condition. update tbConditionComponent set Status = 1 where SurveyUID in ('4DEC2241-43D8-DE11-B935-0017A4770806', '33536B73-43DB-DE11-B935-0017A4770806' etc... The query churns away for a while, then returns the following error message. Msg 512, Level 16, State 1, Procedure trConditionComponentHistory, Line 88 Subquery returned more than 1 value. This is not permissted when the subquery follows =, !=, <, <=, >, >=, or when the subquery is used as an expression. The statement has been terminated. I'm struggling to see why this error occurs when my select statement returns the data I want. I sense it has something to do with when it mentions trConditionComponentHistory but I've no idea where this is comding from. Any ideas? Many thanks,
I'm willing to bet that trConditionComponentHistory is a trigger on the table you are trying to update, and it's been coded to expect only one row at a time will be updated.
It looks like you have a trigger on the table you are trying to update on. Run the following query to find out what trigger is on that table: SELECT OBJECT_NAME(object_id) TriggerName, definition TriggerDefinition FROM sys.sql_modules AS SM WHERE object_id IN (SELECT object_id FROM sys.triggers AS T WHERE parent_id = OBJECT_ID('tbConditionComponent ')) You will see the name and the definition of the trigger. I am guessing tha the trigger calls the stored procedure `trConditionComponentHistory` and that is not written to deal with multiple data changes.
