SQL多表更新问题：能否同时更新t1、t2两张表的指定字段

核心问题答复：是否支持同时更新两个表 #

不同数据库的语法支持存在明确差异：

• MySQL支持单条UPDATE语句关联多张表，同时更新多个表的字段
• PostgreSQL、Oracle、SQL Server等主流数据库不支持单条UPDATE同时更新两张及以上表的字段，需拆分执行，或用事务包裹多条UPDATE语句保证原子性

你原来的写法执行失败主要有三个问题：

1. UPDATE子句仅声明了t1表，没有引入要更新的t2表，数据库无法识别t2的字段
2. SET多字段赋值的语法错误，括号内不能直接写=赋值，规范写法为SET (col1,col2,col3) = (val1,val2,val3)
3. 子查询的关联逻辑和过滤条件混乱，没有和外层更新的表做有效关联

修正后的语法实现 #

场景1：使用MySQL数据库，支持单条UPDATE同时更新两个表 #

如果要将t2对应字段设为常量5，写法如下：

UPDATE test1_00 t1
-- 先关联要更新的t2表
JOIN test2 t2 ON t1.id = t2.id
-- 你的业务筛选条件
WHERE t2.test = '对应过滤值' AND t2.test2 = '对应过滤值'
SET 
  t1.field1 = 't1字段1的取值',
  t1.field2 = 't1字段2的取值',
  t2.field = 5;

如果要从自定义函数返回的结果中取数赋值，写法如下：

UPDATE test1_00 t1
JOIN test2 t2 ON t1.id = t2.id
-- 关联函数返回的结果集
JOIN (
  SELECT 
    test_col1,
    test_col2,
    test_col3,
    id -- 关联用的主键字段
  FROM TABLE(test_function(
    02172,
    'TEST',
    DATE('2021-07-26'),
    'TEST',
    5455612
  )) AS func_res
) res ON t1.id = res.id
WHERE t2.test = '对应过滤值' AND t2.test2 = '对应过滤值'
SET 
  t1.field1 = res.test_col1,
  t1.field2 = res.test_col2,
  t2.field = res.test_col3;

场景2：使用PostgreSQL/Oracle等不支持多表同时更新的数据库 #

用事务包裹两条UPDATE语句，保证更新的原子性，示例如下：

BEGIN;
-- 先更新t1的两个字段
UPDATE test1_00 t1
SET (field1, field2) = (
  SELECT test_col1, test_col2
  FROM TABLE(test_function(
    02172,
    'TEST',
    DATE('2021-07-26'),
    'TEST',
    5455612
  )) AS func_res
  WHERE func_res.id = t1.id
)
WHERE EXISTS (
  SELECT 1 FROM test2 t2 
  WHERE t1.id = t2.id 
    AND t2.test = '对应过滤值' 
    AND t2.test2 = '对应过滤值'
);

-- 再更新t2的对应字段为常量5
UPDATE test2 t2
SET field = 5
WHERE EXISTS (
  SELECT 1 FROM test1_00 t1 
  WHERE t1.id = t2.id 
    AND t2.test = '对应过滤值' 
    AND t2.test2 = '对应过滤值'
);
COMMIT;

注意事项 #

• 执行更新操作前，务必先执行对应关联逻辑的SELECT语句，验证筛选范围和赋值结果是否符合预期，避免误更新数据
• 生产环境执行前建议先开启事务，验证更新行数符合预期后再提交，出现异常可以直接回滚

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