Error in Update Command

Question

Error in Update Command

ShuaibV

SSCommitted

Points: 1999
More actions
March 9, 2009 at 3:29 pm

#202127

Hi
I am getting below mentioned error while executing an Update statement.
Error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.
The statement has been terminated.
My sample update query
Update a set
Col1 = B.Col1,
COL2 = B.Col2
FROM
A JOIN B ON A.Col3 = B.Col3
I dont see any duplicate values Col3 on both the tables. Can anyone let me know to resolve this?
Thanks in advance !!!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 1

There must be something you aren't showing us. There is no subquery shown in your example. The update statement you show will run without an error regardless of whether or not you force duplicates in column three of table A or table B.

The error message you are getting says that you are trying to set some value based on a subquery that is returning more than one row.

set A.ColX = (select B.ColY from B where date >= '1/1/2000')

Please post the complete code, and a little more information about what you are trying to do, and we can try to work this out.

create table #a (Col1 int, Col2 int, Col3 int)

create table #b (Col1 int, Col2 int, Col3 int)

insert into #a

select 0,0,1 union all

select 0,0,2 union all

select 0,0,2

insert into #b

select 1,1,1 union all

select 2,2,2 union all

select 3,3,2

Update #a

set Col1 = b.Col1

,CoL2 = b.Col2

FROM #A a

JOIN #B b ON a.Col3 = b.Col3

Select * from #a

drop table #a

drop table #b

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

ShuaibV SSCommitted Points: 1999 More actions · Answer 2

I just found that the error came from an Update trigger. I didn't realize while executing the script. I have disabled the trigger and executed the scripts without any error.

Thanks

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 3

You're welcome. Good luck 🙂

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills