UPDATE Subquery Returning No Rows in SQL Server

Q

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

✍: FYIcenter.com

A

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

2016-11-02, 1344🔥, 0💬