Multiple row update in MySQL

Question

I have two tables.

support_table

+------+-------------+
| num  | num_explain |
+------+-------------+
| 1    | 01          |
| 2    | 01          |
| 2    | 02          |
| 3    | 01          |
| 3    | 02          |
| 3    | 03          |
| 4    | 01          |
| 4    | 02          |
| 4    | 03          |
| 4    | 04          |
| 5    | 01          |
| 5    | 02          |
| 5    | 03          |
| 5    | 04          |
| 5    | 05          |
+------+-------------+

class_room

+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| seq_no          | varchar(20) | YES  |     | NULL    |       |
| name            | varchar(20) | YES  |     | NULL    |       |
| subjects        | varchar(20) | YES  |     | NULL    |       |
| no_of_student   | varchar(20) | YES  |     | NULL    |       |
| student_roll_no | varchar(20) | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

Now I've tried the below query to insert data into table class_room:

INSERT INTO class_room (seq_no,name,subjects,no_of_student,student_roll_no)
SELECT '1', 'class11', 'physics', num, num_explain FROM support_table 
WHERE num='3';

this query works totally fine for me and it creates 3 rows. Now the table looks like below:

+---------+---------+----------+---------------+-----------------+
| seq_no  | name    | subjects | no_of_student | student_roll_no |
+---------+---------+----------+---------------+-----------------+
| 1       | class11 | physics  | 3             | 01              |
| 1       | class11 | physics  | 3             | 02              |
| 1       | class11 | physics  | 3             | 03              |
+---------+---------+----------+---------------+-----------------+

Now I want to update this table, so I've tried the below code:

UPDATE class_room 
SET name='class11', subjects='chemistry', no_of_student = 
         (SELECT num_explain FROM support_table WHERE num='4') 
WHERE seq_no='1';

But this query IS showing that

Subquery returns more than one row.

Here I want that in class_room table no_of_student will be changed to '4' and student_roll_no will be upto '04' and instead of 3 rows, 4 rows will be created.

Answer 1

You have 3 rows in the table but you expect finally to get 4 rows.
This can't be done with an UPDATE statement which does not add new rows.
The simplest way to do what you want is to delete the current rows and then insert:

delete from class_room where no_of_student = 3;

insert into class_room (seq_no,name,subjects,no_of_student,student_roll_no) 
select '1', 'class11','chemistry',num,num_explain 
from support_table 
where num='4'; 

See the demo.

| seq_no | name    | subjects  | no_of_student | student_roll_no |
| ------ | ------- | --------- | ------------- | --------------- |
| 1      | class11 | chemistry | 4             | 1               |
| 1      | class11 | chemistry | 4             | 2               |
| 1      | class11 | chemistry | 4             | 3               |
| 1      | class11 | chemistry | 4             | 4               |

Answer 2

= Can be used when the subquery returns only 1 value.

When subquery returns more than 1 value, you will have to use IN :

UPDATE class_room set name='class11',subjects='chemistry',no_of_student IN (select num_explain FROM support_table WHERE num='4')LIMIT 1 WHERE seq_no='1';

Example :

select * 
from table
where id IN (multiple row query);

Another example :

SELECT *
FROM Students
WHERE Marks = (SELECT MAX(Marks) FROM Students)   --A Example Subquery returning 1 value

SELECT *
FROM Students
WHERE Marks IN 
      (SELECT Marks 
       FROM Students 
       ORDER BY Marks DESC
       LIMIT 10)                       --Example Subquery returning 10 values

A good explanation can be found here by @Raging bull