How to create an Update statement from a subquery in TSQL

Question

I need to update all records that match my criteria. But the Sql below is giving this error:

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

-- Set MasterAccountId = NULL where there is no Receivable with equivalent BillingAccountId and TaskAccountId 
UPDATE R
SET R.MasterAccountId = NULL  
FROM Receivable R
WHERE EXISTS ( SELECT * FROM MasterAccount M 
                WHERE (ISNULL(M.BillingAccountId, 0) > 0 AND M.BillingAccountId = R.BillingAccountId) OR 
                      (ISNULL(M.TaskAccountId, 0) > 0 AND M.TaskAccountId = R.TaskAccountId))

Basically, I need to update all records that return in that subquery.

Does any one know how to fix it?

Which table do you want to update: `MasterAccount` or `Receivable`? — Blorgbeard, Aug 04 '15 at 02:36
Also, I don't think that SQL will produce the quoted error. Did you copy the right code? — Blorgbeard, Aug 04 '15 at 02:37
I want to update: Receivable.MasterAccountId, I want to set NULL in that field for all the records in receivable that have the matching BillingAccountId or TaskAccountId in the MasterAccount table. @Blorgbeard — Roger Oliveira, Aug 04 '15 at 02:39
Yes, I ran the same Sql in Sql Server the complete error is: Msg 512, Level 16, State 1, Procedure SetReceivableMasterAccount, Line 47 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. — Roger Oliveira, Aug 04 '15 at 02:40

score 1 · Answer 1 · answered Aug 04 '15 at 02:44

I don't think you are getting the said error in posted query May be somewhere else. Again in your EXISTS subquery, instead of saying select * ... it's always better to say WHERE EXISTS ( SELECT 1 FROM MasterAccount M

Also try using the JOIN version of this query instead like

UPDATE R
SET R.MasterAccountId = NULL  
FROM Receivable R
JOIN MasterAccount M ON M.BillingAccountId = R.BillingAccountId 
OR M.TaskAccountId = R.TaskAccountId
WHERE ISNULL(M.BillingAccountId, 0) > 0 
OR ISNULL(M.TaskAccountId, 0) > 0;

I am so sorry guys, but I found the problem. I have another trigger, this error comes from the trigger... :( — Roger Oliveira, Aug 04 '15 at 04:38

score 1 · Accepted Answer · edited May 23 '17 at 10:28

Can you give a try on this. This is base from the repond of https://stackoverflow.com/users/40655/robin-day on this link How do I UPDATE from a SELECT in SQL Server?.

UPDATE 
    R
SET
    R.MasterAccountId = NULL 
FROM
    Receivable R
INNER JOIN
    MasterAccount M
ON
    (ISNULL(M.BillingAccountId, 0) > 0 AND M.BillingAccountId = R.BillingAccountId) OR 
    (ISNULL(M.TaskAccountId, 0) > 0 AND M.TaskAccountId = R.TaskAccountId))

How to create an Update statement from a subquery in TSQL

2 Answers2