基于Table1列值更新Table2列：合并UPDATE查询结果异常原因及解决

需求：根据Table1中Lcolumn的值更新Table2的L1、L2列：当Table1的Lcolumn = "L1"时，更新Table2的L1列；当Lcolumn = "L2"时更新L2列。

表结构 #

Table1 #

Table2（初始状态） #

分开执行更新的可行方案 #

分别执行针对L1和L2的更新语句，可以得到预期结果：

更新L1的SQL：

UPDATE table2 B
LEFT JOIN table1 A
ON( A.fnr = B.fnr AND A.gnr = B.gnr AND A.wnr = B.wnr AND A.Lcolumn = "L1" )
SET 
    B.L1= A.Lcode 
WHERE A.Lcode IS NOT NULL

更新L2的SQL（逻辑类似）：

UPDATE table2 B
LEFT JOIN table1 A
ON( A.fnr = B.fnr AND A.gnr = B.gnr AND A.wnr = B.wnr AND A.Lcolumn = "L2" )
SET 
    B.L2= A.Lcode 
WHERE A.Lcode IS NOT NULL

执行后得到预期结果：

合并执行的问题 #

尝试将两个查询合并为一个时，实际结果与预期不符：每个wnr仅更新其中一列，另一列仍为NULL。

合并后的SQL：

UPDATE table2 B
LEFT JOIN table1 A
ON( A.fnr = B.fnr AND A.gnr = B.gnr AND A.wnr = B.wnr )
SET 
  B.L1= IF( A.Lcolumn = "L1" , A.Lcode , B.L1 ), 
  B.L2= IF( A.Lcolumn = "L2" , A.Lcode , B.L2 )
WHERE A.Lcode IS NOT NULL

问题原因 #

当用LEFT JOIN关联时，Table2的每一行会和Table1中匹配的多行（比如wnr=49对应Table1的两行）生成多条关联记录，但MySQL的UPDATE语句处理同一条目标行（Table2的一行）的多次匹配时，只会保留最后一次匹配的更新结果。

以wnr=49为例：

1. 第一条关联记录对应Table1中Lcolumn='L1'的行，此时更新L1为19，L2保持初始的NULL；
2. 第二条关联记录对应Table1中Lcolumn='L2'的行，此时更新L2为29，但L1会被重置为初始的NULL（因为IF条件不满足，取B.L1，而此时B.L1的更新还未持久化，仍是初始值）。

最终同一条Table2的行只会保留最后一次关联的更新结果，导致只有一列被更新。

合并执行的解决方案 #

方案：先聚合Table1再关联更新 #

先通过子查询将Table1的数据转换为宽表结构（每个(fnr,gnr,wnr)对应一行，包含L1和L2的有效值），再与Table2关联更新，避免多次匹配的问题：

UPDATE table2 B
JOIN (
    SELECT 
        fnr, gnr, wnr,
        MAX(CASE WHEN Lcolumn = 'L1' THEN Lcode END) AS L1_val,
        MAX(CASE WHEN Lcolumn = 'L2' THEN Lcode END) AS L2_val
    FROM table1
    WHERE Lcode IS NOT NULL
    GROUP BY fnr, gnr, wnr
) A ON A.fnr = B.fnr AND A.gnr = B.gnr AND A.wnr = B.wnr
SET 
    B.L1 = COALESCE(A.L1_val, B.L1),
    B.L2 = COALESCE(A.L2_val, B.L2);

说明：

• 子查询用CASE和MAX聚合，把每个(fnr,gnr,wnr)对应的L1、L2有效值提取出来，形成一行数据；
• 用COALESCE确保如果聚合后的值为NULL（比如没有对应Lcolumn的有效数据），则保留Table2原有的值；
• 一次关联即可完成两列的更新，结果与分开执行一致。

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