December 2, 2009 at 2:22 pm
I'm trying to update a field in one table with a value from another table:
update PS_Students2
set spcd =
(
select
se.spcode
from SpecEd0708 se
inner join PS_Students2 ps on ps.studentid = se.studid
and ps.SCHYR2 = se.schyear
and ps.schyr2 = '2007'
)
I have one studentid/studid per year with multiple years per table. Following returns one unique row per student for a the 2007 school year:
select
se.spcode
from SpecEd0708 se
inner join PS_Students2 ps on ps.studentid = se.studid
and ps.SCHYR2 = se.schyear
and ps.schyr2 = '2007'
The error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I'm sure I'm just missing something simple ...
December 2, 2009 at 2:28 pm
Hi,
SQL Server does not let you assign a value to a column when it COULD be returning more than 1 row. In this example, I don't think you need to return the data from a subquery, you can simply use the "UPDATE FROM" syntax to do this:
Here's how:
UPDATE ps
SET ps.spcd = se.spcode
FROM SpecEd0708 se
INNER JOIN PS_Students2 ps ON ps.studentid = se.studid
AND ps.SCHYR2 = se.schyear
AND ps.schyr2 = '2007'
By the way, the solution is not tested, since I do not have your table definitions. But I think it should work.
Hope that helps,
Cheers,
J-F
December 2, 2009 at 2:29 pm
You're not relating the subquery in any way to the table that you're updating, so, for each row of the table that you're updating, ALL of the rows in the subquery match. Not allowed.
The PS_Students2 (aliased as ps) in the subquery is not considered the one that you're updating. The one in the subquery is only visible inside the subquery and, once aliased, doesn't even match the name of the table that you're updating.
Besides, you don't need a subquery at all.
update ps
set spcd = se.spcode
from SpecEd0708 se
inner join PS_Students2 ps on ps.studentid = se.studid
and ps.SCHYR2 = se.schyear
and ps.schyr2 = '2007'
Should work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2009 at 2:33 pm
THANKS! I seem to try and make it more difficult than it needs to be. Really appreciate the these forums and the what I learned from them.
K
December 2, 2009 at 2:37 pm
J-F Bergeron (12/2/2009)
SQL Server does not let you assign a value to a column when it COULD be returning more than 1 row.
It does allow the scenario where returning multiple rows is possible, the error occurs when a subquery really does return more than one value. I can hack up a demo if you like, is pretty late here, so not tonight.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2009 at 2:44 pm
GilaMonster (12/2/2009)
J-F Bergeron (12/2/2009)
SQL Server does not let you assign a value to a column when it COULD be returning more than 1 row.It does allow the scenario where returning multiple rows is possible, the error occurs when a subquery really does return more than one value. I can hack up a demo if you like, is pretty late here, so not tonight.
Gail, you're probably right about this. I thought it was preventing that kind of errors when parsing the query.
Anyway, I'm just happy I could get to the same solution you did, 😉
Cheers,
J-F
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply