如何用原生SQL批量更新Post表type字段？关联多表自动赋值

问题 #

现有三个关联表，需要基于Forum表的值批量更新Post表的type字段：

表结构与关联规则 #

• Post表：字段 p_id、u_id、type（新增字段，初始为NULL）
• User表：字段 u_id（与Post的u_id关联）、f_id；每个用户仅属于一个论坛，一个论坛可包含多个用户
• Forum表：字段 f_id（与User的f_id关联）、type、value；f_id与type构成联合主键；仅关注type = 'RELEVANT_TYPE'的记录

示例数据 #

Post表（初始状态）

User表

Forum表

预期输出（Post表更新后） #

当前做法是手动迭代Forum的所有value值，逐行执行以下SQL：

update Post set type = <VALUE> where u_id in (
  select u_id from (
    select distinct u_id, f_id from User where u_id in (
      select u_id from Post)) as t
    join Forum f on t.f_id = f.f_id where f.type = 'RELEVANT_TYPE' and f.value = <VALUE>)

该方法可行，但效率低且需手动操作，希望找到更优的原生SQL实现方式。

优化方案 #

可以通过多表关联更新一次性完成所有Post记录的更新，无需手动迭代。以下是主流数据库的实现：

1. MySQL/MariaDB #

使用UPDATE ... JOIN语法直接关联三张表，过滤目标Forum记录后批量更新：

UPDATE Post p
JOIN User u ON p.u_id = u.u_id
JOIN Forum f ON u.f_id = f.f_id AND f.type = 'RELEVANT_TYPE'
SET p.type = f.value;

2. PostgreSQL #

通过UPDATE ... FROM语法实现多表关联更新：

UPDATE Post p
SET type = f.value
FROM User u
JOIN Forum f ON u.f_id = f.f_id AND f.type = 'RELEVANT_TYPE'
WHERE p.u_id = u.u_id;

3. SQL Server #

使用UPDATE ... FROM关联多表完成更新：

UPDATE p
SET p.type = f.value
FROM Post p
INNER JOIN User u ON p.u_id = u.u_id
INNER JOIN Forum f ON u.f_id = f.f_id AND f.type = 'RELEVANT_TYPE';

逻辑说明 #

• 直接通过u_id关联Post与User，再通过f_id关联User与Forum
• 仅匹配Forum中type = 'RELEVANT_TYPE'的记录，确保取到目标value值
• 一次性完成全量Post记录的type字段更新，避免手动迭代的繁琐，同时利用数据库关联优化提升执行效率

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