What MySQL returns if sub-query, used to assign new values in the SET clause of UPDATE statement, returns no rows?

In this case, MySQL will provide a NULL value to the SET clause. Following example will demonstrate it −

Example

mysql> Select * from student; +----+---------+-----------+ | Id | Name    | grade     | +----+---------+-----------+ | 1  | Rahul   | Good      | | 2  | Gaurav  | Good      | | 3  | Raman   | Excellent | | 4  | Harshit | Average   | | 5  | Aarav   | Best      | | 6  | Ram     | average   | +----+---------+-----------+ 6 rows in set (0.00 sec) mysql> select * from info; +------+-----------+ | id   | remarks   | +------+-----------+ | 1    | Good      | | 2    | Good      | | 3    | Excellent | | 4    | Average   | | 5    | Best      | +------+-----------+ 5 rows in set (0.00 sec)

From the above two table, we can see ‘student’ table is having 6 rows and the value of ‘grade’ is ‘average’ where id = 6 and ‘info’ table is having 5 rows. Now the sub-query used in the following query will provide no rows to give new values in SET clause hence it will insert a NULL value. It can be examined from the output of following queries −

mysql> UPDATE STUDENT SET grade = (SELECT remarks from info WHERE info.id = student.id) WHERE id = 6; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> Select * from student; +----+---------+-----------+ | Id | Name    | grade     | +----+---------+-----------+ | 1  | Rahul   | Good      | | 2  | Gaurav  | Good      | | 3  | Raman   | Excellent | | 4  | Harshit | Average   | | 5  | Aarav   | Best      | | 6  | Ram     | NULL      | +----+---------+-----------+ 6 rows in set (0.00 sec)