sql - 对于每个选定的行插入另一个？

Question

我有一个表 TableA。它有列 id、type、relatedId、another1、another2。列 type 可以有值 1、2 或 3。我需要的是，对于 TableA 中 type = 1 的每一行，在同一个表中插入另一行并使用 更新原始行(relatedId 列)新插入行的 id。此外，新插入行中某些列的值应从原始行中复制。

所以对于当前状态:

id|type|relatedId|another1

10| 1  |null|"some text"
11| 2  |null|"somthing"
12| 1  |null|"somthing else"

结果应该如下:

id|type|relatedId|another1

10| 1  |13  |"some text"      - now has relationship to 13
11| 2  |null|"somthing"
12| 1  |14  |"somthing else"  - now has relationship to 13
13| 3  |null|"some text"      - inserted, "another1" is copied from 10
14| 3  |null|"somthing else"  - inserted, "another1" is copied from 12

Answer 1

假设文本是唯一的，您可以这样做:

demo:db<>fiddle

WITH ins AS (
    INSERT INTO tablea(type, related_id, another1)
    SELECT 3, null, another1
    FROM tablea
    WHERE type = 1
    RETURNING id, another1
)
UPDATE tablea t
SET related_id = s.id
FROM (
    SELECT * FROM ins
) s
WHERE s.another1 = t.another1 AND t.type = 1

WITH 子句允许顺序执行两个单独的语句。所以首先插入新数据。使用新生成的 ID，您可以在之后更新旧数据。因为您必须匹配原始数据，所以文本作为标识符很有帮助。

这仅在您不必使用 (1, 'something') 的数据集时才有效。那么就很难确定这两个记录中的哪一个是每份副本的原件。

---

另一种方法是将 type1-ids 也存储在新的 type3-columns 中。如果这对你来说没问题，你可以这样做:

demo:db<>fiddle

WITH ins AS (
    INSERT INTO tablea(type, related_id, another1)
    SELECT 3, id, another1
    FROM tablea
    WHERE type = 1
    RETURNING id, related_id, another1
)
UPDATE tablea t
SET related_id = s.id
FROM (
    SELECT * FROM ins
) s
WHERE s.related_id = t.id

这会将原始类型 1-id 存储在新类型的 related_id 列中。因此在任何情况下都可以通过该值找到原始 ID。

不幸的是，您不能在另一个 WITH 子句中将这些列 NULL 掉，因为 WITH 子句仅适用于现有数据。此时查询本身还没有完成。所以新记录在物理上并不存在。

---

这个可以工作...

demo:db<>fiddle

WITH to_be_copied AS (
    SELECT id, another1
    FROM tablea
    WHERE type = 1
), ins AS (
    INSERT INTO tablea(type, related_id, another1)
    SELECT 3, null, another1
    FROM to_be_copied
    ORDER BY id                         -- 1
    RETURNING id, another1
)
UPDATE tablea t
SET related_id = s.type3_id
FROM (
SELECT 
    * 
FROM 
    (SELECT id as type1_id, row_number() OVER (ORDER BY id) FROM to_be_copied) tbc 
    JOIN 
    (SELECT id as type3_id, row_number() OVER (ORDER BY id) FROM ins) i 
    ON tbc.row_number = i.row_number
) s
WHERE t.id = s.type1_id

此解决方案假定 (1) 处的给定顺序可确保新记录的插入顺序。事实上，我不太确定。但如果这样:首先查询所有type1记录。之后有复制(以相同的顺序!)。之后，旧的和新的记录 ID 被获取。 row_number() 窗口函数将连续的行计数添加到记录中。因此，如果两个数据集的顺序相同，则旧 ID 应获得与其对应的新 ID 相同的行号。在那种情况下，识别是可能的。对于这个工作的小例子......

--> 编辑:这似乎在说:是的，自 Postgres 9.6 https://stackoverflow.com/a/50822258/3984221 起将保留顺序。