Collections:
UPDATE with Subquery Returning No Rows in MySQL
What Happens If the UPDATE Subquery Returns No Rows in MySQL?
✍: 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, MySQL will provide a NULL value to the SET clause. The tutorial exercise below shows you a good example:
mysql> UPDATE fyi_links SET notes = 'Number one' WHERE id = 0; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT id, url, notes, counts FROM fyi_links WHERE id = 0; +----+-------------------+------------+--------+ | id | url | notes | counts | +----+-------------------+------------+--------+ | 0 | www.fyicenter.com | Number one | NULL | +----+-------------------+------------+--------+ 1 row in set (0.00 sec) mysql> SELECT * FROM fyi_rates WHERE id = 0; Empty set (0.00 sec) mysql> UPDATE fyi_links SET notes = ( SELECT comment FROM fyi_rates WHERE fyi_rates.id = fyi_links.id ) WHERE id = 0; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT id, url, notes, counts FROM fyi_links WHERE id = 0; +----+-------------------+-------+--------+ | id | url | notes | counts | +----+-------------------+-------+--------+ | 0 | www.fyicenter.com | NULL | NULL | +----+-------------------+-------+--------+ 1 row in set (0.00 sec)
2018-01-08, 842👍, 0💬
Popular Posts:
Where to find Oracle database server tutorials? Here is a collection of tutorials, tips and FAQs for...
Where to find answers to frequently asked questions on Using User Defined Functions in SQL Server Tr...
What are DDL (Data Definition Language) statements for tables in SQL Server? DDL (Data Definition La...
How To Create a View on an Existing Table in SQL Server? If you want to a view on an existing table,...
Where to find answers to frequently asked questions on SQL Transaction Management in Oracle? Here is...