mysql返回新数据表的子查询_MySQL如何将具有SELECT子查询的表插入返回多行的表？...

MySQL如何将具有SELECT子查询的表插入返回多行的表？

MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?

INSERT INTO Results

(

People,

names,

)

VALUES

(

(

SELECT d.id

FROM Names f

JOIN People d ON d.id = f.id

),

(

"Henry"

),

);

我想用此子查询返回的所有结果填充新表。 我如何做到这一点而又没有收到错误1242(21000)：子查询返回的行多于1

7个解决方案

108 votes

INSERT INTO Results (People, names )

SELECT d.id, 'Henry'

FROM Names f

JOIN People d ON d.id = f.id

将静态字符串Henry与SELECT查询结合在一起。

Ryan answered 2020-01-02T18:12:35Z

10 votes

INSERT INTO Results

(

People,

names,

)

VALUES

(

(

SELECT d.id

FROM Names f

JOIN People d ON (d.id = f.id) limit 1

),

(

"Henry"

),

);

Umar Enesi Ibrahim answered 2020-01-02T18:12:50Z

8 votes

这就是我发现的效果很好。 它有点长，但很多时候都需要重新整理多余的数据。

使用值将多行从table2插入table1。例子：

INSERT INTO table1 (col1, col2, col3, col4, col5)

SELECT col1,col2,col3,col4,col5

FROM table2 t2

WHERE t2.val2 IN (MULTIPLE VALUES)

AND (Another Conditional);

您可以插入硬编码的值，以插入具有重复数据的多行：

INSERT INTO table1 (col1, col2, col3, col4, col5)

SELECT "Value", col2, col3, "1900-01-01","9999-12-31"

FROM table2 t2

WHERE t2.val2 IN (MULTIPLE VALUES)

AND (Another Conditional);

注意：“ Value”，“ 1900-01-01”，“ 9999-12-31”将在所有插入的行中重复。

MiggityMac answered 2020-01-02T18:13:24Z

6 votes

INSERT INTO Results

(

People,

names,

)

SELECT d.id, 'Henry'

FROM Names f

JOIN People d ON d.id = f.id

triclosan answered 2020-01-02T18:13:39Z

1 votes

出现此错误(子查询返回多于1行)的原因是您使用了括号()。 最好的答案看起来要格外小心。 它不包含围绕子查询的解析

Sveteek answered 2020-01-02T18:13:59Z

1 votes

在MySql中，可以像下面这样插入字符串中的多个值，避免重复。 谢谢。

insert into brand(name) select * from (

select 'Fender' as name

union select 'a'

union ..... ) t

where not exists (select 1 from brand t2 where t2.name COLLATE latin1_general_ci = t.name COLLATE utf8mb4_unicode_ci )

Fabio Marano answered 2020-01-02T18:14:19Z

-1 votes

插入ec_element(parentid，name)中，从ec_element中选择elementid'STARTUP'，其中name ='BG';

insert语句从满足条件和标签字符串的表中获取元素id的值。

sahmad answered 2020-01-02T18:14:44Z