我在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。问题是,我从select中的insert查询得到的记录不止一条。我怎样才能消除这种错误。

最佳答案

总查询如下所示。在这里,将*替换为要选择的列名!

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语句移到一个子查询中,这样您就可以使用子查询的总结果。

关于mysql - sql中insert语句内的select语句返回多行,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/36030370/

10-16 15:27