SQL Server error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or

Question

I am getting the following error while running the query in SQL Server:

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

Can anyone please help me out

My code looks like this :

UPDATE a
SET a.id = (SELECT f.id
            FROM f 
            JOIN a ON a.fun = [f].funny
            WHERE a.fun = [f].funny);

The error says it all. The `SELECT` is returning more than one record, and you can't set `a.id` to multiple values. Perhaps you can modify the `SELECT` to identify a single record? — David, May 31 '22 at 16:41
It means this bit: `SELECT f.id FROM f Join a ON a.fun= [f].funny WHERE a.fun= [f].funny` returned more than one row. And clearly you can't set your `a.id` to have multiple values at once. So SQL is complaining that it doesn't know which value to use, or what to do. We can't tell you why it returns multiple rows because we can't see your schema or data, but you need to resolve that issue somehow and make it return only 1 row. Either that or you need to rethink your approach - we don't actually know what your requirements are for this query. — ADyson, May 31 '22 at 16:41
Sounds like it should be a straight `update...from` then, without a subquery. That's what I meant when I said you may need to rethink your approach — ADyson, May 31 '22 at 17:03
`... Join a ON a.fun= [f].funny WHERE a.fun= [f].funny);` That WHERE clause does nothing useful. Given that, it seems you need to take a step back and think more clearly about your goal. What we can say is that for any value of a.fun there can be (and are) multiple rows in f.funny. You need to figure out what value to assign to the column a.id in those cases. Perhaps the max value? Maybe you need a very different query? No one but you can know. — SMor, May 31 '22 at 17:09

score 0 · Accepted Answer · answered May 31 '22 at 17:00

0

you can try update with join

update t1 set t1.col = t2.col
from table1 t1
join table2 t2 on t2.id = t1.id
where conditions ...

answered May 31 '22 at 17:00

1 Answers1