CODEWITHSUNDEEP
mysqlnestedsubquery
psatek
psatek

Reputation: 135

Inserting multiple rows per subquery in mysql

I would like to do an insert using a select and then insert multiple rows per return value from the sub query for example

INSERT INTO t1 (column1, column2, column3)
SELECT column1_value,5,10 FROM t2;

would insert 5 into column2 for each row and 10 into each row for column three. Is there anyway that I can pass multiple values here to get one row with 5 in column 2 and another row with 7 per record obtained from the select from t2?

Upvotes: 1

Views: 1652

Answers (2)

CptMisery
CptMisery

Reputation: 614

There's lots of ways to insert "this or that". Barmar listed two examples, but there's also IF, IFNULL, NULLIF, and CASE:

http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html

Upvotes: 0

Barmar
Barmar

Reputation: 780994

Use UNION

INSERT INTO t1 (column1, column2, column3)
SELECT column1_value, 5, 10 FROM t2
UNION ALL
SELECT column1_value, 5, 7 FROM t2

You could also join with a UNION

INSERT INTO t1 (column1, column2, column3)
SELECT t2.column1_value, 5, t3.column3_value
FROM t2
CROSS JOIN (SELECT 10 AS column3_value
            UNION
            SELECT 7 AS column3_value) AS t3

Upvotes: 2

Related Questions