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, 1489🔥, 0💬
Popular Posts:
How To Start MySQL Server in MySQL? If you want to start the MySQL server, you can run the "mysqld" ...
How To Look at the Current SQL*Plus System Settings in Oracle? If you want to see the current values...
What Is an Oracle Tablespace in Oracle? An Oracle tablespace is a big unit of logical storage in an ...
What Is a Dynamic Performance View in Oracle? Oracle contains a set of underlying views that are mai...
How To Set Up SQL*Plus Output Format in Oracle? If you want to practice SQL statements with SQL*Plus...