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

more ▼

asked Dec 06, 2011 at 03:56 AM in Default

avatar image

1 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Dec 06, 2011 at 04:13 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

lightning fingers..... beat me by 2 minutes.

Dec 06, 2011 at 04:17 AM WilliamD

Both of you get a +1

Dec 06, 2011 at 05:16 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Dec 06, 2011 at 04:17 AM

avatar image

26.2k 18 38 48

  • ah but you provided some code......

Dec 06, 2011 at 04:32 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 06, 2011 at 03:56 AM

Seen: 2479 times

Last Updated: Dec 06, 2011 at 03:56 AM

Copyright 2018 Redgate Software. Privacy Policy