sql - 使用许多更新语句重构 PostgreSQL WITH 查询

Question

我有一串需要按顺序执行的 SQL 查询，每个查询都需要一个 WITH 查询。像这样:

WITH diff AS (
  SELECT NOW() - created_at as time_diff FROM table_a ORDER BY created_at DESC LIMIT 1
)
UPDATE table_x SET created_at = created_at + (SELECT time_diff FROM diff);

WITH diff AS (
  SELECT NOW() - created_at as time_diff FROM table_a ORDER BY created_at DESC LIMIT 1
)
UPDATE table_y SET created_at = created_at + (SELECT time_diff FROM diff);

WITH diff AS (
  SELECT NOW() - created_at as time_diff FROM table_a ORDER BY created_at DESC LIMIT 1
)
UPDATE table_a SET created_at = created_at + (SELECT time_diff FROM diff);

这是一个稍微做作的例子，但意图是“移动”一定数量的表的所有记录的created_at 时间戳。因为 table_a 包含在所有 WITH 查询中，所以它必须是最后更新的表，否则其他一切都会出错。

理想情况下，我只想做这样的事情:

WITH diff AS (
  SELECT NOW() - created_at as time_diff FROM table_a ORDER BY created_at DESC LIMIT 1
)
UPDATE table_x SET created_at = created_at + (SELECT time_diff FROM diff)
UPDATE table_y SET created_at = created_at + (SELECT time_diff FROM diff)
UPDATE table_a SET created_at = created_at + (SELECT time_diff FROM diff);

既因为它更简洁，也因为这样顺序就不再重要了。但这显然不是合法的语法。

有没有办法在单个查询中完成此操作？

Answer 1

您需要将每个更新(除了最后一个)放在 CTE 中:

WITH diff AS (
  SELECT NOW() - created_at as time_diff FROM table_a ORDER BY created_at DESC LIMIT 1
), upd_x as (
  UPDATE table_x SET created_at = created_at + (SELECT time_diff FROM diff)
), upd_y as (
  UPDATE table_y SET created_at = created_at + (SELECT time_diff FROM diff)
)
UPDATE table_a SET created_at = created_at + (SELECT time_diff FROM diff);