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.

You are asking to update one row at a time by changing the values of `name`, `subjects` and `no_of_student`... but the catch here is that you are asking *at the same time* to select ***multiple*** values of `num_explain` into `no_of_student`. — pensum, Dec 07 '19 at 06:51

score 1 · Accepted Answer · answered Dec 07 '19 at 06:34

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               |

Selaka Nanayakkara · Answer 2 · 2019-12-07T06:32:57.933

0

= 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

edited Dec 07 '19 at 06:32

answered Dec 07 '19 at 06:23

Selaka Nanayakkara

3,296
1
22
42

this doesn't look right for a SET. Maybe LIMIT 1 would work – Garr Godfrey Dec 07 '19 at 06:29
still sub querty will return multiple and got the same rror – Devsi Odedra Dec 07 '19 at 06:29

Multiple row update in MySQL

2 Answers2