sql - INSERT 多行与 RETURNING 子句中的 ID 组合

Question

这是我的查询:

WITH rows AS (INSERT INTO t_patch(f_name)VALUES('KB000')RETURNING f_patch_id)
INSERT INTO t_patch_available (f_patch_id, f_product_id)
SELECT f_patch_id,7 FROM rows;

它有效。但是，如果我想插入多个具有相同 f_patch_id 的 t_patch_available，语法是什么？

Answer 1

您可以在选择上使用联合来添加更多行。

WITH rows AS (INSERT INTO t_patch(f_name)VALUES('KB001')RETURNING f_patch_id)
INSERT INTO t_patch_available (f_patch_id, f_product_id) 
SELECT f_patch_id,7 FROM rows 
UNION ALL SELECT f_patch_id, 8 FROM rows