PostgreSQL非规范化数据库缺失字段跨表更新需求咨询

针对给定3表的分步实现 #

按照需求顺序执行以下SQL语句：

1. 更新VIS表ID=1的Created Date #

从DTA表ID=1的对应字段取值：

UPDATE vis
SET "Created Date" = (SELECT "Created Date" FROM dta WHERE id = 1)
WHERE id = 1;

2. 更新DTA表ID=2的Created Date #

从VIS表ID=2的对应字段取值：

UPDATE dta
SET "Created Date" = (SELECT "Created Date" FROM vis WHERE id = 2)
WHERE id = 2;

3. 更新CRT表的Created Date #

先设置ID=3的字段值，再计算所有表的最大时间戳更新ID=1、2：

-- 先更新CRT表ID=3，取VIS表ID=3的日期
UPDATE crt
SET "Created Date" = (SELECT "Created Date" FROM vis WHERE id = 3)
WHERE id = 3;

-- 计算VIS、DTA、CRT三表的最大Created Date，更新CRT表ID=1、2
WITH all_max_created AS (
    SELECT MAX("Created Date") AS max_val FROM vis
    UNION ALL
    SELECT MAX("Created Date") FROM dta
    UNION ALL
    SELECT MAX("Created Date") FROM crt
)
UPDATE crt
SET "Created Date" = (SELECT MAX(max_val) FROM all_max_created)
WHERE id IN (1, 2);

扩展到6张表+Created/Updated双字段的通用思路 #

1. 交叉字段更新：如果需要从表X的IDn字段更新表Y的IDn字段，直接复用以下模板：

UPDATE target_table
SET target_column = (SELECT source_column FROM source_table WHERE id = target_table.id)
WHERE id = n;

（若需批量更新多个ID，可将WHERE id = n改为WHERE id IN (n1, n2, ...)，确保子查询返回对应ID的唯一值）

2. 多表极值更新：如果需要将某表的指定ID字段设置为多个表的最大/最小时间戳，用CTE汇总所有目标表的对应字段极值，再批量更新：

WITH all_max_timestamps AS (
    SELECT MAX("Created Date") AS max_created, MAX("Updated Date") AS max_updated FROM table1
    UNION ALL
    SELECT MAX("Created Date"), MAX("Updated Date") FROM table2
    UNION ALL
    SELECT MAX("Created Date"), MAX("Updated Date") FROM table3
    -- 继续添加剩余3张表的查询
)
UPDATE target_table
SET 
    "Created Date" = (SELECT MAX(max_created) FROM all_max_timestamps),
    "Updated Date" = (SELECT MAX(max_updated) FROM all_max_timestamps)
WHERE id IN (target_ids);

3. 注意事项：

• 字段名带空格需用双引号包裹，避免PostgreSQL解析错误。
• MAX()函数会自动忽略-infinity值，无需额外处理。
• 执行前建议先运行SELECT语句验证取值是否正确，避免误更新：

  -- 验证VIS表ID=1更新后的值
  SELECT (SELECT "Created Date" FROM dta WHERE id=1) AS new_value FROM vis WHERE id=1;
  

内容的提问来源于stack exchange，提问作者xRavisher