CODEWITHSUNDEEP
sqlt-sqlsubquery
TLD
TLD

Reputation: 8135

How to use Subquery which returned more than one result

My SQL query

UPDATE Task SET Done = 1 WHERE (ID = (SELECT ID FROM User WHERE UserName = @UserName)

I tried and got 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 statement has been terminated.

Is there any possible way to do that (for example a for loop)? Thanks

Upvotes: 2

Views: 970

Answers (4)

Irvin Dua
Irvin Dua

Reputation: 771

UPDATE Task SET Done = 1 WHERE ID IN (SELECT ID FROM User WHERE UserName = @UserName)

Upvotes: 1

HBublitz
HBublitz

Reputation: 680

How about the following. You can use simple joins the same way you do in SELECT statements:

UPDATE Task t, User u 
SET t.Done = 1 
WHERE t.ID = u.ID
AND u.UserName = @UserName

Upvotes: 0

a&#39;r
a&#39;r

Reputation: 36999

You can use the IN operator.

WHERE ID IN (SELECT ID FROM User WHERE UserName = @UserName)

Upvotes: 4

Dan Grossman
Dan Grossman

Reputation: 52372

How would a row's ID be equal to more than one value?

That doesn't make sense, which is why writing such a query isn't possible.

Checking if the ID is in a set would make sense, though...

...WHERE ID IN (SELECT ID...

Upvotes: 6

Related Questions