Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server Subquery returned more than 1 value. This is not permitted when the subquery follows (chars) or when the subquery is used as an expression

I am trying to update some fields based on their occurence. If they only occur one time, I am updating some status field.

My current code is as follows:

UPDATE table1
SET statusField = 1
WHERE someID = (
               SELECT someID
               FROM table1
               GROUP BY someID HAVING COUNT(*) = 1
               )

This returns an error like the one in the title: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Is there any other, as easily readable/simple, solution to this?

like image 411
Nict Avatar asked Apr 07 '14 10:04

Nict


3 Answers

Use IN keyword instead of equals operator like so:

UPDATE table1
SET statusField = 1
WHERE someID IN (
           SELECT someID
           FROM table1
           GROUP BY someID HAVING COUNT(*) = 1
           )

Using = requires that exactly 1 result is returned by the subquery. IN keyword works on a list.

like image 139
shree.pat18 Avatar answered Nov 17 '22 04:11

shree.pat18


You should join your tables in the subselect. It is possible to use 'in', but in your case I would use exists:

UPDATE table1 x
SET statusField = 1
WHERE exists (
               SELECT null
               FROM table1
               WHERE x.someID = someID
               GROUP BY someID 
               HAVING COUNT(*) = 1
               )

For better performance I would use this script instead (sqlserver-2008+):

;WITH x as
(
SELECT rc = count() over (partition by someID), statusField
FROM table1
)
UPDATE x
SET statusField = 1
WHERE rc = 1
like image 41
t-clausen.dk Avatar answered Nov 17 '22 02:11

t-clausen.dk


Try this

Use Top

UPDATE table1
SET statusField = 1
WHERE someID = (
               SELECT TOP 1 someID
               FROM table1
               GROUP BY someID HAVING COUNT(*) = 1
               )

Or you can use IN clause

UPDATE table1
SET statusField = 1
WHERE someID IN (
               SELECT someID
               FROM table1
               GROUP BY someID HAVING COUNT(*) = 1
               )
like image 44
Vignesh Kumar A Avatar answered Nov 17 '22 04:11

Vignesh Kumar A