What will MySQL return if the subquery used to assign a new value in the SET clause of an UPDATE statement returns multiple rows?

In this case, MySQL will return an error message because we know that if a subquery is used to assign a new value in the SET clause of the UPDATE statement, then it must be updated A WHERE clause is returned for each matching row in the table.

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 have seen above, the "info" table has two rows with id = 5, so when we use it in a subquery, it will return multiple rows, Therefore MySQL will return an error message like this -

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

The above is the detailed content of What will MySQL return if the subquery used to assign a new value in the SET clause of an UPDATE statement returns multiple rows?. For more information, please follow other related articles on the PHP Chinese website!