question

Konzy avatar image
Konzy 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,
sqlupdateerrorsubquery
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

lightning fingers..... beat me by 2 minutes.
0 Likes 0 ·
Both of you get a +1
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 ah but you provided some code......
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.