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)
⇒ Error: Subquery Returns More than 1 Row in MySQL
⇐ UPDATE Using Data from Other Tables in MySQL
2018-01-08, 2097🔥, 0💬
Popular Posts:
How to download and install the scaled-down database AdventureWorksLT in SQL Server? If you want to ...
How To End a Stored Procedure Properly in SQL Server Transact-SQL? Where the end of the "CREATE PROC...
How To Create a Stored Program Unit in Oracle? If you want to create a stored program unit, you can ...
Where to find answers to frequently asked questions on Storage Engines: MyISAM, InnoDB and BDB in My...
How To Recover a Dropped Index in Oracle? If you have the recycle bin feature turned on, dropped ind...