mysql - 使用 select 语句插入多行

Question

我有两个表“class”和“class1”。

类别:

+------+---------+---------+
| name | classid | rollnum |
+------+---------+---------+
| ram  | 10      | 1       |
| ram  | 10      | 2       |
| ram  | 20      | 1       |
| ram  | 20      | 2       |
| ram  | 20      | 3       |
+------+---------+---------+

现在我想执行以下给定的查询:

insert into class1 (name1,classid1,rollnum1) values((select name from class where name='ram' and classid='10'),(select classid from class where name='ram' and classid='10'),(select rollnum from class where name='ram' and classid='10'));

因此“class1”表如下所示:

+-------+----------+----------+------+
| name1 | classid1 | rollnum1 | add1 |
+-------+----------+----------+------+
| ram   | 10       | 1        | NULL |
| ram   | 10       | 2        | NULL |
+-------+----------+----------+------+

但是上面的查询给了我以下错误:

ERROR 1242 (21000): Subquery returns more than 1 row

Answer 1

使用一个 SELECT 语句代替 VALUES:

insert into class1 (name1,classid1,rollnum1) 
select name,classid,rollnum 
from class 
where name='ram' and classid='10'