mysql - MySQL 中的多行更新

Question

我有两张 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          |
+------+-------------+

教室

+-----------------+-------------+------+-----+---------+-------+
| 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    |       |
+-----------------+-------------+------+-----+---------+-------+

现在我尝试使用以下查询将数据插入表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';

这个查询对我来说完全正常，它创建了 3 行。现在表格如下所示:

+---------+---------+----------+---------------+-----------------+
| 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              |
+---------+---------+----------+---------------+-----------------+

现在我想更新这个表，所以我尝试了以下代码:

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

但是这个查询表明

Subquery returns more than one row.

在这里，我希望在 class_room 表中 no_of_student 将更改为“4”，而 student_roll_no 将变为“04”，而不是将创建 3 行、4 行。

Answer 1

表中有 3 行，但您希望最终得到 4 行。
这无法通过 不添加新行的 UPDATE 语句来完成。
执行您想要的操作的最简单方法是删除当前行，然后插入:

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'; 

请参阅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               |