MS SQL Server Interview Question:

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

Submitted by: Administrator

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 ggl_links (id, url, notes)
VALUES (0, 'www.globalguideline.com', 'Number one')
GO
(1 row(s) affected)

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

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

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

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

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