select statement inside insert statement in sql returns multiple rows

Question

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)
);

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.

then use : insert into table (value1, value2, value3) select x,y,z from table2 — lordkain, Mar 16 '16 at 08:38
Your subqueries (`select` queries) is returning more than one row I guess. I would suggest to try adding `limit 1` in each `select` query and then try once if it goes alright then the query is correct you need to find a way to limit the rows returned. — Deepesh, Mar 16 '16 at 08:42
Yes, select is returning more than one row, but I want to insert them all. — VijayRana, Mar 16 '16 at 08:44
Then can you use stored procedure for this? (the only solution I know to loop upon the data) — Deepesh, Mar 16 '16 at 08:45
duplicate with http://stackoverflow.com/questions/5253302/insert-into-select-for-all-mysql-columns — lordkain, Mar 16 '16 at 08:57
@lordkain It is not duplicate. The referred question deals with only one subquery — Thanga, Mar 16 '16 at 08:59
same problem, same answer, sounds duplicate with me. but back to vijayrana, is this helpfull for you? — lordkain, Mar 16 '16 at 09:00
i don't think this is dublicate to refered link anyway thanks for answering. but still stucked — VijayRana, Mar 16 '16 at 09:14

lordkain · Answer 1 · 2016-03-16T10:52:25.123

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.

Would you please explain :) – VijayRana Mar 16 '16 at 10:02 — VijayRana, Mar 16 '16 at 10:02

select statement inside insert statement in sql returns multiple rows

1 Answers1

Linked