如何用单条SQL语句同步更新关联的orders与delivery表字段

首先先提两个一不留神就会踩的坑：

• 核对字段真实拼写：你描述里提到orders表的字段一会是order_recieved（少打个e的拼写）、一会是order_received，实际跑SQL前一定要对着表结构确认真实字段名，不然执行直接报未知字段的错，白折腾
• 整个更新流程必须用事务包起来，千万别把两个表的更新拆成单独的语句跑，不然很容易出现订单表改成功了、配送表改失败的情况，两边状态对不上，后续查数据全是麻烦

分数据库适配的更新语句模板 #

不同数据库的跨表更新语法有差异，根据实际用的数据库选对应写法即可：

1. 适配MySQL这类支持单条多表联更的数据库 #

可以直接在一条语句里关联两表完成更新，减少数据库交互次数：

-- 开启事务
START TRANSACTION;

UPDATE orders o
INNER JOIN delivery d 
  ON o.order_no = d.orderFK -- 按主键、外键的关联关系匹配两表记录
SET 
  -- 替换成实际的收货状态字段名，值和业务约定的枚举完全对齐
  o.order_received = 'Received', 
  d.delivery_status = 'complete'
WHERE 
  -- 替换成前端点击按钮传过来的目标订单号，绝对不能漏这个条件
  o.order_no = #{当前操作的订单号}
  -- 增加前置状态判断，避免误改历史已完成的订单
  AND o.order_received IS NULL 
  AND d.delivery_status = 'order shipped';

-- 确认两个表的更新行数符合预期（单条订单操作一般是1行更新）再提交，出现异常直接执行ROLLBACK回滚
COMMIT;

2. 适配PostgreSQL、Oracle这类不支持单条多表联更的数据库 #

需要在同一个事务里分开执行两个更新语句：

-- 开启事务
BEGIN TRANSACTION;

-- 第一步更新订单表的收货标记
UPDATE orders
SET order_received = 'Received'
WHERE 
  order_no = #{当前操作的订单号}
  AND order_received IS NULL;

-- 第二步更新配送表的状态
UPDATE delivery
SET delivery_status = 'complete'
WHERE 
  orderFK = #{当前操作的订单号}
  AND delivery_status = 'order shipped';

-- 校验两个表的更新行数都和预期匹配后再提交，任意一条没更新到目标值都要回滚
COMMIT;

关键注意事项 #

• 所有更新语句的WHERE条件必须带上「记录原来的状态值」作为过滤条件，就算用户重复点击按钮，也不会把已经完成的订单重复覆盖
• 绝对不能省略目标订单号的过滤条件，否则会把全表符合状态的订单全部批量改掉，引发生产事故
• 写状态值的时候要和业务定义的枚举完全对齐，注意拼写和大小写，比如常见的把Received拼写成Recieved的错误，要和实际存储的枚举值匹配，不然会出现状态值不统一的脏数据

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