CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Ryan Smith
Ryan Smith

Reputation: 87

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

Upvotes: 0

Views: 115

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28900

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

Upvotes: 3

Related Questions