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, 1416🔥, 0💬
Popular Posts:
How To Use SQL*Plus Built-in Timers in Oracle? If you don't have a stopwatch/timer and want to measu...
Can Date and Time Values Be Converted into Integers in SQL Server Transact-SQL? Can date and time va...
How To Install PHP on Windows in MySQL? The best way to download and install PHP on Windows systems ...
What Is an Oracle Instance in Oracle? Every running Oracle database is associated with an Oracle ins...
How To Format Time Zone in +/-hh:mm Format in SQL Server Transact-SQL? From the previous tutorial, y...