Collections:
UPDATE Subquery Returning No Rows in SQL Server
What Happens If the UPDATE Subquery Returns No Rows in SQL Server?
✍: FYIcenter.com
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.
2016-11-02, 642👍, 0💬
Popular Posts:
How To Calculate Age in Days, Hours and Minutes in SQL Server Transact-SQL? On many Web sites, news ...
How To Import One Table Back from a Dump File in Oracle? If you only want to import one table back t...
How to connect SQL Server Management Studio Express to SQL Server 2005 Express in SQL Server? Once y...
How To Round a Numeric Value To a Specific Precision in SQL Server Transact-SQL? Sometimes you need ...
Where to find SQL Server database server tutorials? Here is a collection of tutorials, tips and FAQs...