sql中插入语句中的select语句返回多行 [英] select statement inside insert statement in sql returns multiple rows
问题描述
我在 mysql 中有以下结构化查询.但它返回一个错误.基本上,我想从现有表中获取价值并插入新表.我尝试了以下操作,但出现错误;
I have a following structured query in mysql. But its returning a error. Basically, I want to get value from existing table and instert into new table. I tried the following, but got error;
INSERT INTO `table1`(
`first`,`second`,`third`) VALUES(
(SELECT table2.timemodified FROM `xtable` AS table2,`ytable` AS table3
WHERE table3.id = table2.contextid),
(SELECT table4.id FROM `ztable` AS table4,`ytable` AS table3 WHERE table4.id = table3.instanceid),
(SELECT murs.id FROM `table5` AS murs,
`xtable` AS table2,
`wtable` AS table6,
`ytable` AS table3,
`vtable` AS table7
WHERE murs.id = table2.userid AND table6.id = table2.roleid AND table3.id = table2.contextid AND table7.instance = table3.instanceid AND table6.id =3)
);
我测试过,但错误是:#1242 - 子查询返回超过 1 行
.问题是我从 insert
内的 select
查询中获得了多个记录.我怎样才能消除这样的错误.
I tested but the error is : #1242 - Subquery returns more than 1 row
. The problem is I am getting more than single record from the select
query inside insert
. How can I remove such error.
推荐答案
整个查询如下所示.将 * 替换为要选择的列名!
the total query looks something like below. where you have so replace the * with column names you want to select!
INSERT INTO table1(first,second,third)
-- replace * with columns name first,second,third
select * from (
-- START YOU'RE select query
(SELECT table2.timemodified FROM `xtable` AS table2,`ytable` AS table3
WHERE table3.id = table2.contextid),
(SELECT table4.id FROM `ztable` AS table4,`ytable` AS table3 WHERE table4.id = table3.instanceid),
(SELECT murs.id FROM `table5` AS murs,
`xtable` AS table2,
`wtable` AS table6,
`ytable` AS table3,
`vtable` AS table7
WHERE murs.id = table2.userid AND table6.id = table2.roleid AND table3.id = table2.contextid AND table7.instance = table3.instanceid AND table6.id =3)
-- END YOU'RE select query
)
我将您的选择语句移到了子查询中,以便您可以利用子查询的总结果对您有利.
I moved you're select statements into a subquery so you can use the total result of the subquerys to you're advantage.
这篇关于sql中插入语句中的select语句返回多行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!