SQL INNER JOIN多表关联UPDATE更新语句报错排查

原SQL存在4个核心语法/逻辑问题：

• 跨表更新语法不兼容：多数数据库的单条UPDATE语句默认仅支持更新单表字段，原语句同时对batch、batchsop两张不同表的字段赋值，不符合基础语法规则；即便是支持多表更新的MySQL，原语句写的UPDATE b SET ... FROM 表的写法也不是MySQL的合法多表更新语法（该写法是SQL Server的语法，且SQL Server同样不支持单条语句更新多表）。
• 聚合函数使用错误：SUM()是分组聚合函数，必须搭配GROUP BY明确聚合维度才能返回单批次的汇总值，直接写在SET子句中关联未聚合的产量表，既会触发语法报错，也会在一个批次对应多条产量记录时出现计算错误。
• 关联字段不匹配：根据给出的表结构，三张表的关联外键是batch_id，原SQL关联条件写的是b.id = byl.batch_id、b.id = bsop.batch_id，引用了batch表不存在的id字段，会触发未知字段报错。
• 字段歧义风险：WHERE条件中的end_date、batch_status未指定所属表别名，多表关联场景下如果其他关联表存在同名字段，会触发字段歧义报错。

根据使用的数据库类型，可选两种写法：

写法1：MySQL专属多表更新写法（性能更高） #

MySQL支持特殊的多表更新语法，可单条语句完成两张表的更新，注意必须先预聚合每个批次的总产量，避免一对多关联导致数据重复计算：

UPDATE 
    igrow.farm_management_batch b
INNER JOIN (
    -- 先按批次聚合计算总收获量
    SELECT batch_id, SUM(actual_harvest) AS total_harvest
    FROM igrow.farm_management_batchyield
    GROUP BY batch_id
) byl ON b.batch_id = byl.batch_id
INNER JOIN igrow.sop_management_batchsopmanagement bsop 
    ON b.batch_id = bsop.batch_id
SET
    b.batch_status = 'completed',
    b.stage = 'flowering',
    b.actual_produce = byl.total_harvest,
    bsop.current_status = '3'
WHERE 
    b.end_date < '2022-07-10 00:00:00.000000'
    AND b.end_date IS NOT NULL
    AND b.batch_status IN ('running', 'to_start');

写法2：全数据库兼容事务写法（通用性最强） #

PostgreSQL、Oracle等数据库不支持单条语句更新多表，可通过事务包裹两条更新语句，保证操作原子性（两次更新要么全部成功，要么全部回滚，不会出现只更新了一张表的脏数据）：

-- 开启事务
BEGIN;

-- 更新batch表字段，通过关联子查询计算单批次总收获量
UPDATE igrow.farm_management_batch b
SET 
    batch_status = 'completed',
    stage = 'flowering',
    actual_produce = (
        SELECT SUM(actual_harvest)
        FROM igrow.farm_management_batchyield byl
        WHERE byl.batch_id = b.batch_id
    )
WHERE 
    end_date < '2022-07-10 00:00:00.000000'
    AND end_date IS NOT NULL
    AND batch_status IN ('running', 'to_start');

-- 更新对应批次的sop表状态
UPDATE igrow.sop_management_batchsopmanagement bsop
INNER JOIN igrow.farm_management_batch b 
    ON bsop.batch_id = b.batch_id
SET bsop.current_status = '3'
WHERE 
    b.end_date < '2022-07-10 00:00:00.000000'
    AND b.end_date IS NOT NULL
    AND b.batch_status = 'completed';

-- 提交事务
COMMIT;

操作提示：正式执行更新前，建议将语句替换为SELECT查询，先校验待更新的记录范围、总产量计算结果是否符合预期，确认无误后再执行更新，避免误改生产数据。

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