What Happens If the UPDATE Subquery Returns No Rows in SQL Server?

If you use a subquery to assign new values in the SET clause in an UPDATE statement, and the subquery returns no rows for an outer row, SQL Server will provide a NULL value to the SET clause. The tutorial exercise below shows you a good example:

-- insert a new row
INSERT INTO fyi_links (id, url, notes) 
  VALUES (0, 'www.fyicenter.com', 'Number one')
GO
(1 row(s) affected)

-- view old values
SELECT * FROM fyi_links WHERE id = 0
GO
id    url                  notes        counts  created
0     www.fyicenter.com    Number one   NULL    2007-05-23

-- make sure there is no matching row in fyi_rates
SELECT * FROM fyi_rates WHERE id = 0
GO
0 rows

-- update a subquery returning no rows
UPDATE fyi_links SET notes = (
      SELECT comment FROM fyi_rates 
      WHERE fyi_rates.id = fyi_links.id
   ) 
   WHERE id = 0
(1 row(s) affected)

-- view new values
SELECT * FROM fyi_links WHERE id = 0
GO
id    url                  notes        counts  created
0     www.fyicenter.com    NULL         NULL    2007-05-23

Column "notes" gets updated with NULL if there is no return rows in the subquery.

⇒ UPDATE Subquery Returning Multiple Rows in SQL Server

⇐ Using Values from Other Tables in UPDATE Statements in SQL Server

⇑ Using INSERT, UPDATE and DELETE Statements in SQL Server

⇑⇑ SQL Server Database Tutorials