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,
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.