使用 "with" 子句时使用新行的 ID 更新现有记录

Question

平台：Ruby on Rails与PostgreSQL数据库。

问题：我们正在进行一些回填，以便将数据迁移到一个新的结构中。这造成了一个相当复杂的情况，我们希望尽可能有效地处理它。它部分是用类似于以下的SQL解决的：

with rows as (
insert into responses (prompt_id, answer, received_at, user_id, category_id)
select prompt_id, null as answer, received_at, user_id, category_id
from prompts
where user_status = 0 and skipped is not true
returning id, category_id
)
insert into category_responses (category_id, response_id)
select category_id, id as response_id
from rows;

表和列已被模糊/简化，因此其背后的原因可能不那么清楚，但category_responses是一个多对多联接表。我们正在做的是获取现有的提示，并为每个提示创建一组空响应(答案为NULL(。

缺少的部分是将提示中的记录与新创建的响应相关联。有没有一种方法可以在查询中做到这一点？如果可能的话，我希望避免在答案中添加prompt_id列，但我猜这将是处理这一问题的一种方法，将其包含在returning子句中，然后发出第二个查询来更新提示表——无论如何，我甚至不确定是否可以使用单个with子句的结果运行多个查询。

实现这一目标的最佳方法是什么？

Answer 1

我已经决定添加所需的列，并更新查询如下：

with tab1 as (
insert into responses (prompt_id, answer, received_at, user_id, category_id, prompt_id)
select prompt_id, null as answer, received_at, user_id, category_id
from prompts
where user_status = 0 and skipped is not true
returning id, category_id, prompt_id
),
tab2 as (
update prompts
set response_id = tab1.response_id,
category_id = tab1.category_id
from tab1
where prompts.id = tab1.prompt_id
returning prompts.response_id as response_id, prompts.category_id as category_id
)
insert into category_responses (category_id, response_id)
select category_id, id as response_id
from tab2;