我想使用select执行一次插入,然后在每个子查询的返回值中插入多行,例如
INSERT INTO t1 (column1, column2, column3)
SELECT column1_value,5,10 FROM t2;将在每行的column2中插入5,在第三列的每行中插入10。我是否可以在这里传递多个值,以便从select from t2中获得一个行,其中第2列中有5个值,另一行中每条记录有7个值?
发布于 2016-09-24 00:51:03
使用UNION
INSERT INTO t1 (column1, column2, column3)
SELECT column1_value, 5, 10 FROM t2
UNION ALL
SELECT column1_value, 5, 7 FROM t2您还可以使用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发布于 2016-09-24 03:29:00
插入"this or that“的方法有很多种。Barmar列出了两个示例,但也有IF、IFNULL、NULLIF和CASE:
http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html
https://stackoverflow.com/questions/39665665
复制相似问题