The simple ones really stump me...

Question

Post reply

The simple ones really stump me...

Christine M

Old Hand

Points: 393
More actions
December 18, 2007 at 2:49 pm

#184833

I have this simple update query...
update CEM_smcsAssignments
set MGR =
(SELECT a.MGR
FROM #ADD a, CEM_smcsAssignments c
WHERE a.SSN = c.OWNbr)
I want to get the data from one table, and update the other table. I get this error.
Server: Msg 512, Level 16, State 1, Line 1
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.

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

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Unlike Oracle or other RDBMS's, you can do this in SQL Server...

UPDATE CEM_smcsAssignments

SET MGR = a.MGR

FROM #ADD a,

CEM_smcsAssignments c

WHERE a.SSN = c.OWNbr

If it'll make you feel better, you can also do this...

UPDATE c

SET MGR = a.MGR

FROM #ADD a,

CEM_smcsAssignments c

WHERE a.SSN = c.OWNbr

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 2

Christine M (12/18/2007)
I have this simple update query...
update CEM_smcsAssignments
set MGR =
(SELECT a.MGR
FROM #ADD a, CEM_smcsAssignments c
WHERE a.SSN = c.OWNbr)
I want to get the data from one table, and update the other table. I get this error.
Server: Msg 512, Level 16, State 1, Line 1
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.

Note that If you use Jeff Moden's code,if there are more than one value then the first/last(not sure which one) returned value would be assigned

Madhivanan

Failing to plan is Planning to fail

Christine M Old Hand Points: 393 More actions · Answer 3

[/quote]

Note that If you use Jeff Moden's code,if there are more than one value then the first/last(not sure which one) returned value would be assigned[/quote]

Thanks for the reminder. I had already selected the correct values into #ADD.