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, 2133🔥, 0💬
Popular Posts:
What is sqlservr.exe - Process - SQL Server (SQLEX?PRESS) in SQL Server? Process sqlservr.exe is the...
How to set database to be READ_ONLY in SQL Server? Databases in SQL Server have two update options: ...
How To Find Out What Privileges a User Currently Has in Oracle? Privileges granted to users are list...
Where to find MySQL database server tutorials? Here is a collection of tutorials, tips and FAQs for ...
How To Fix the INSERT Command Denied Error in MySQL? The reason for getting the "1142: INSERT comman...