Ryan Smith Ryan Smith - 1 month ago 5
SQL Question

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, etc

I have a column that has to be updated in a table (

tbl_two
) that references the ID from another table (
tbl_one
).

My current update statement looks like this:

UPDATE tbl_two
SET col_idnum =
(SELECT tbl_one.col_enrollmentid FROM dbo.tbl_one
JOIN dbo.tbl_two ON tbl_one.appid = tbl_two.appid
WHERE tbl_two.programid = 132
AND tbl_one.isfirst = 1
AND tbl_one.programtypeid = 132)
WHERE col_programid = 132


This throws an 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 nested select statement only returns only column (the
tbl_one.col_enrollmentid
) but it does return 47,804 rows of results. Which is what it should return. That's the intended result.

I've searched here for solutions but all I've found is to use an
IN
statement as to the
=
statement, but I don't think it's possible to perform an update using an 'IN' statement.

Can anyone please help me with this query in SQL Server 2008?

Thanks in advance

Answer

use join..

UPDATE  t2
SET col_idnum = t1.col_enrollmentid 
from
dbo.tbl_one t1
JOIN dbo.tbl_two t2
 ON tbl_one.appid = tbl_two.appid
        and  t1.programid = 132
        AND t1.isfirst = 1
        AND t2.programtypeid = 132