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

MySQLMySQLi Database

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)
raja
Published on 19-Feb-2018 12:08:40
Advertisements