JPA多表关联UPDATE查询语法求助：适配MySQL/SQLServer语法问题

基础语法错误修正 #

原语句存在两个核心语法问题：

• SET子句中每个字段的赋值操作必须用逗号分隔
• a.fourthItem =?8缺少空格，需改为a.fourthItem = ?8

适配MySQL的原生SQL语句 #

MySQL支持UPDATE ... JOIN的多表更新语法，修正后的完整语句如下：

UPDATE FirstTable a 
INNER JOIN SecondTable b ON a.secondTableId = b.secondTableId
SET 
    a.firstItem = ?1,
    a.secondItem = ?2,
    b.firstItem = ?3,
    b.secondItem = ?4,
    b.thirdItem = ?5,
    b.fourthItem = ?6,
    a.thirdItem = ?7,
    a.fourthItem = ?8
WHERE 
    a.ID = ?9 
    AND a.environment = ?10

适配SQL Server的原生SQL语句 #

SQL Server的多表更新更稳妥的方式是拆分为两个独立语句，避免语法兼容性问题：

-- 更新FirstTable
UPDATE a
SET 
    a.firstItem = ?1,
    a.secondItem = ?2,
    a.thirdItem = ?7,
    a.fourthItem = ?8
FROM FirstTable a
INNER JOIN SecondTable b ON a.secondTableId = b.secondTableId
WHERE 
    a.ID = ?9 
    AND a.environment = ?10;

-- 更新SecondTable
UPDATE b
SET 
    b.firstItem = ?3,
    b.secondItem = ?4,
    b.thirdItem = ?5,
    b.fourthItem = ?6
FROM SecondTable b
INNER JOIN FirstTable a ON a.secondTableId = b.secondTableId
WHERE 
    a.ID = ?9 
    AND a.environment = ?10;

JPQL的限制说明 #

如果是使用JPA的JPQL（而非原生SQL），JPQL的UPDATE语句不支持直接关联多个实体更新，只能针对单个实体操作，需拆分为两个独立JPQL语句：

1. 更新FirstTable

UPDATE FirstTable a 
SET 
    a.firstItem = ?1,
    a.secondItem = ?2,
    a.thirdItem = ?7,
    a.fourthItem = ?8
WHERE 
    a.ID = ?9 
    AND a.environment = ?10

2. 更新SecondTable

UPDATE SecondTable b 
SET 
    b.firstItem = ?3,
    b.secondItem = ?4,
    b.thirdItem = ?5,
    b.fourthItem = ?6
WHERE 
    b.secondTableId = (SELECT a.secondTableId FROM FirstTable a WHERE a.ID = ?9 AND a.environment = ?10)

内容的提问来源于stack exchange，提问作者No longer in college