如何将多表UPDATE SET SQL拆分为查询待更新数据与执行更新的两条语句

方案1：使用数据库原生RETURNING子句（最优选择，仅1次数据库交互） #

这是效率最高的实现方式，不需要拆分两次查询，更新操作执行的同时直接返回需要追踪的字段，全程只有一次数据库请求，天然保证数据一致性，不存在并发修改导致的ID匹配错误问题。

支持范围：PostgreSQL全版本、MySQL 8.0.20+、Oracle 12c+ 均支持该语法
改造后代码示例：

// UPDATE语句末尾追加RETURNING子句，指定需要返回的追踪字段
String updateSql =  "UPDATE table1 tbl1, table2 tbl2 " +
                    "SET tbl1.status=:status, tbl2.status=:status " +
                    "WHERE ... " +
                    "RETURNING tbl1.id AS tbl1_id, tbl2.id AS tbl2_id";

// 直接调用getResultList接收返回的更新行数据，不需要执行executeUpdate
List<Object[]> updatedIds = entityManager.createNativeQuery(updateSql)
                    .setParameter("status", SomeStatus.NEW)
                    .getResultList();

// updatedIds的长度即为被更新的行数，遍历即可获取所有ID做追踪
for (Object[] row : updatedIds) {
    Long table1UpdatedId = (Long) row[0];
    Long table2UpdatedId = (Long) row[1];
    // 此处补充你的ID记录逻辑
}

方案2：低版本数据库兼容方案（2次数据库交互，保证一致性） #

如果使用的数据库版本不支持RETURNING语法，再考虑拆分查询+更新的实现，必须加悲观锁避免查询和更新的间隙有其他事务修改数据，导致拿到的ID和实际更新行不匹配，两个步骤必须放在同一个事务内执行保证原子性。
实现步骤：

• 第一步：执行SELECT ... FOR UPDATE查询符合条件的待更新行ID，对匹配行加行锁
• 第二步：使用完全相同的过滤条件执行UPDATE操作，保证更新的就是加锁的行
  代码示例：

// 第一步：加锁查询待更新ID
String selectSql = "SELECT tbl1.id, tbl2.id FROM table1 tbl1, table2 tbl2 WHERE ... FOR UPDATE";
List<Object[]> toUpdateIds = entityManager.createNativeQuery(selectSql)
                    .getResultList();

if (toUpdateIds.isEmpty()) {
    // 无符合条件的数据，直接返回
    return Collections.emptyList();
}

// 第二步：执行更新，过滤条件和查询完全一致
String updateSql =  "UPDATE table1 tbl1, table2 tbl2 " +
                    "SET tbl1.status=:status, tbl2.status=:status " +
                    "WHERE ...";
entityManager.createNativeQuery(updateSql)
                    .setParameter("status", SomeStatus.NEW)
                    .executeUpdate();

// toUpdateIds即为本次实际更新的行ID集合，可直接用于追踪
return toUpdateIds;

注意事项 #

• 禁止使用无锁查询后直接更新，高并发场景下会出现查询后、更新前其他事务修改了匹配数据，导致记录的ID和实际更新行不一致
• 如果单次更新的行数超过1000，建议分批处理，避免单次操作锁表时间过长影响业务
• 方案1的性能比方案2高40%以上，数据库版本支持的情况下优先选择方案1

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