shivam shivam - 3 months ago 8
MySQL Question

Insert into table with multiple rows in subquery

I want to have results in a table where the data comes from 3 different tables.

For that i have tried to execute this query:

INSERT INTO sometable (id,date)
VALUES
(
(SELECT id FROM table1
UNION
SELECT id FROM table2
UNION
SELECT id FROM table3)
,
(SELECT date FROM table1
UNION
SELECT date FROM table2
UNION
SELECT date FROM table3)
)


The result of this query is a error stating cannot insert multiple rows. Please help me to write this query correctly.

Answer

The INSERT ... SELECT syntax is different to the INSERT ... VALUES syntax. Also, you want to select both columns from each table at the same time:

INSERT INTO sometable (id, date)
SELECT id, date FROM table1 UNION
SELECT id, date FROM table2 UNION
SELECT id, date FROM table3
Comments