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.rendc.org', '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.rendc.org 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.rendc.org NULL NULL 2007-05-23

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