CODEWITHSUNDEEP
sqlselectsql-update
Nigel Tunnicliffe
Nigel Tunnicliffe

Reputation: 131

Update SQL record only if select does not return null

I have a SQL table of customers and their parent customers where I need to update the customer table to show the customers owner (which is another entry in the customer table). i.e a Customer may exist in their own right or they may (optionally) have a parent record in the same table. I have tried using the SQL command

Update 
  Customers
SET
  Customers.Owner = (SELECT TOP 1 Owner from Customers Customer where CustomerId = Customers.Parent)

but this sets the owner to null if there is no parent. How do I only update the record if the Select statement does not return null. I tried adding a where clause but that only seems to act on the select clause, not on its result.

Upvotes: 0

Views: 809

Answers (2)

Shantanu Kher
Shantanu Kher

Reputation: 1054

You can use MERGE statement as well.

MERGE INTO Customers tgt
USING Customers src
ON (src.CustomerId = tgt.Parent)
WHEN MATCHED THEN 
UPDATE tgt.Owner = src.Owner;

Upvotes: 0

DuhVir
DuhVir

Reputation: 481

You may use EXIST:

Update 
  Customers
SET
  Customers.Owner = (SELECT TOP 1 Owner from Customers Customer 
                     where CustomerId = Customers.Parent)
WHERE EXIST (SELECT TOP 1 Owner from Customers Customer 
             where CustomerId = Customers.Parent)

Upvotes: 3

Related Questions