Subquery returned more than 1 value

[WurzelGummidge]

Any ways around this:
(i get a '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.' statement returned)


update account set seccodeid = (select seccodeid from account where accountid in (
SELECT ACCOUNTID FROM ACCOUNTSUMMARY WHERE SECCODEID <> (SELECT SECCODEID FROM ACCOUNT A2 WHERE
ACCOUNTSUMMARY.ACCOUNTID = A2.ACCOUNTID) ))

 

[dky1e]

You can do select top 1 [...] to retrieve only one value,
but I think you meant to do:
WHERE SECCODEID not in (SELECT SECCODEID FROM ACCOUNT A2 WHERE
ACCOUNTSUMMARY.ACCOUNTID = A2.ACCOUNTID)

 

[royjimenez]

update account set seccodeid =
(select seccodeid from account
where accountid in (
SELECT ACCOUNTID
FROM ACCOUNTSUMMARY
WHERE SECCODEID <>
( SELECT SECCODEID
FROM ACCOUNT A2
WHERE ACCOUNTSUMMARY.ACCOUNTID = A2.ACCOUNTID) ))

when u use an IN the selects could return more than a row, and u are using WHERE SECCODEID <> ...., it's VERY possible then u will return several rows.


if u want to update a several at the same time, u could make you SQL sentence like this :

UPDATE table
SET FIELD1 = t2.FIELD5
FROM table, table2 t2
where table.key = t2.key ....


But i am not sure if it 's all the u need...

regards.

 

[SQLBill]

Wurzel,

Here's an explanation of what's happening, it might help you see what's wrong.

This part of your query can return several values:

(SELECT SECCODEID FROM ACCOUNT A2 WHERE
ACCOUNTSUMMARY.ACCOUNTID = A2.ACCOUNTID)

Let's say it returns:
SECCODEID
1
2
3

Now replace that part of the script with the results:

update account set seccodeid = (select seccodeid from account where accountid in (
SELECT ACCOUNTID FROM ACCOUNTSUMMARY WHERE SECCODEID <> (1,2,3)))

See why <> doesn't work? How can anything be NOT EQUAL to a string of three numbers? However, NOT IN works with strings, which is what that last SELECT returns.

I suggest trying dkyle's suggestion. I believe it should work for you.

-SQLBill