在sql语句中insert语句返回多行

我在mysql中有一个以下结构化查询。 但它返回一个错误。 基本上，我想从现有的表中获得价值，并插入新表中。 我尝试了以下，但得到错误;

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 - Subquery returns more than 1 row 。 问题是我从insert内部的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
)

我将select语句移到了子查询中，这样您就可以使用subquerys的总结果来获得优势。

链接地址: http://www.djcxy.com/p/16883.html

上一篇: select statement inside insert statement in sql returns multiple rows

下一篇: Update multiple rows using select statement