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.
⇒ UPDATE Subquery Returning Multiple Rows in SQL Server
⇐ Using Values from Other Tables in UPDATE Statements in SQL Server
2016-11-02, 1853🔥, 0💬
Popular Posts:
How To Format DATETIME Values to Strings with the CONVERT() Function in SQL Server Transact-SQL? SQL...
How To Use SQL*Plus Built-in Timers in Oracle? If you don't have a stopwatch/timer and want to measu...
How to download and install Microsoft .NET Framework Version 2.0 in SQL Server? .NET Framework Versi...
How To Change the Password for Your Own User Account in MySQL? If you want to change the password of...
Is SQL Server Transact-SQL case sensitive? No. Transact-SQL is not case sensitive. Like the standard...