x

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,

more ▼

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

Konzy gravatar image

Konzy
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

Kev Riley gravatar image

Kev Riley ♦♦
52.7k 47 49 76

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

WilliamD gravatar image

WilliamD
25.9k 17 19 41

+1 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x712
x129
x56
x19

asked: Dec 06, 2011 at 03:56 AM

Seen: 1462 times

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