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


In this case, MySQL will return an error message because we know that if sub-query is used to assign new values in the SET clause of UPDATE statement then it must return exactly one row for each row in the update table that matches the WHERE clause.

Example

mysql> insert into info(id, remarks) values(5,'average');
Query OK, 1 row affected (0.06 sec)

mysql> select * from info;
+------+-----------+
| id   | remarks   |
+------+-----------+
| 1    | Good      |
| 2    | Good      |
| 3    | Excellent |
| 4    | Average   |
| 5    | Best      |
| 5    | average   |
+------+-----------+
6 rows in set (0.00 sec)

As we can see above ‘info’ table is having two rows with id = 5 hence when we will use it in the sub-query it will return multiple rows and due to this MySQL will return an error message as follows −

mysql> UPDATE STUDENT SET grade = (SELECT remarks from info WHERE info.id = student.id) WHERE id = 5;
ERROR 1242 (21000): Subquery returns more than 1 row

karthikeya Boyini
karthikeya Boyini

I love programming (: That's all I know

Updated on: 20-Jun-2020

199 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements