CODEWITHSUNDEEP
t-sqlsql-server-2012
Roger
Roger

Reputation: 1631

How to create an Update statement from a subquery in TSQL

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?

Upvotes: 0

Views: 55

Answers (2)

Neil Villareal
Neil Villareal

Reputation: 637

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))

Upvotes: 1

Rahul
Rahul

Reputation: 77866

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;

Upvotes: 1

Related Questions