What Happens If the UPDATE Subquery Returns No Rows in MySQL?

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)

 

⇒INSERT, UPDATE and DELETE Statements in MySQL

⇒⇒MySQL Database Tutorials