sql - PostgreSQL-根据从另一个表中的选择插入行，并使用新插入的行更新该表中的FK

我正在两个表之间进行数据迁移（分割出一个相关的表）。现有的表是cc，它有一个列和一个新添加的列指向一个新的表，它也有一个列。对于reminders中的每一行，我想在start中复制dateset_id值的新行，并在dateset中使用新插入的startID的相应行。
下面是我尝试的SQL：

WITH inserted_datesets AS (
  INSERT INTO dateset (start)
  SELECT start FROM reminder
  RETURNING reminder.id AS reminder_id, id AS dateset_id
)
UPDATE reminder
SET dateset_id = ids.dateset_id
FROM inserted_datesets AS ids
WHERE reminder.id = ids.reminder_id

我得到一个错误reminders，因为我在INSERT子句中包含了dateset列，但实际上并没有为插入选择它。这是有道理的，但我不知道如何修改查询以满足我的需要。我错过了完全不同的方法吗？

最佳答案

有几种方法可以解决这个问题。
一。临时添加列
正如其他人提到的，直接的方法是暂时在reminder_id中添加一列dateset。用IDs表中的原始reminder填充它。使用它将reminder与dateset表联接。删除临时列。
2。当开始是唯一的
如果start列的值是唯一的，则可以通过将reminder表与dateset列上的start表连接，在没有额外列的情况下执行此操作。

INSERT INTO dateset (start)
SELECT start FROM reminder;

WITH
CTE_Joined
AS
(
    SELECT
        reminder.id AS reminder_id
        ,reminder.dateset_id AS old_dateset_id
        ,dateset.id AS new_dateset_id
    FROM
        reminder
        INNER JOIN dateset ON dateset.start = reminder.start
)
UPDATE CTE_Joined
SET old_dateset_id = new_dateset_id
;

三。当开始不是唯一的
即使在这种情况下也可以不使用临时列。主要思想如下。让我们看看这个例子：
reminder中有两行具有相同的start值和IDs 3和7：

reminder
id    start         dateset_id
3     2015-01-01    NULL
7     2015-01-01    NULL

在我们将它们插入dateset之后，将生成新的id，例如1和2：

dateset
id    start
1     2015-01-01
2     2015-01-01

我们如何连接这两行并不重要。最终的结果可能是

reminder
id    start         dateset_id
3     2015-01-01    1
7     2015-01-01    2

或

reminder
id    start         dateset_id
3     2015-01-01    2
7     2015-01-01    1

这两种变体都是正确的。这就引出了下面的解决方案。
只需先插入所有行。

INSERT INTO dateset (start)
SELECT start FROM reminder;

在start列上匹配/联接两个表，知道它不是唯一的。”通过添加ROW_NUMBER并通过两列连接使其“唯一”。可以缩短查询时间，但我明确地说明了每个步骤：

WITH
CTE_reminder_rn
AS
(
    SELECT
        id
        ,start
        ,dateset_id
        ,ROW_NUMBER() OVER (PARTITION BY start ORDER BY id) AS rn
    FROM reminder
)
,CTE_dateset_rn
AS
(
    SELECT
        id
        ,start
        ,ROW_NUMBER() OVER (PARTITION BY start ORDER BY id) AS rn
    FROM dateset
)
,CTE_Joined
AS
(
    SELECT
        CTE_reminder_rn.id AS reminder_id
        ,CTE_reminder_rn.dateset_id AS old_dateset_id
        ,CTE_dateset_rn.id AS new_dateset_id
    FROM
        CTE_reminder_rn
        INNER JOIN CTE_dateset_rn ON
            CTE_dateset_rn.start = CTE_reminder_rn.start AND
            CTE_dateset_rn.rn = CTE_reminder_rn.rn
)
UPDATE CTE_Joined
SET old_dateset_id = new_dateset_id
;

我希望从代码中可以清楚地看到它的作用，特别是当您将它与没有ROW_NUMBER的简单版本进行比较时。显然，即使start是唯一的，复杂解也会起作用，但它不如简单解有效。
此解决方案假设在此过程之前dateset为空。