如何使用Table B的数据更新MySQL中Table A的指定字段？

首先得提一句：看起来你给的Table B第一列列名应该是reference（你写的“0”大概率是笔误），毕竟数据里的4222、4223和Table A的reference完全匹配，而且同个reference下的记录数也一一对应，我就按这个前提来写方案了。

这个需求的核心难点是同一个reference下的记录要按顺序一一对应更新，不能直接只靠reference关联（否则会出现一对多的匹配错误）。下面分两种MySQL版本给出方案：

一、MySQL 8.0及以上版本（支持窗口函数） #

用窗口函数给每个reference组内的记录生成行号，再通过reference+行号关联两张表，拆分字段后更新：

WITH A_with_row AS (
    SELECT 
        reference, 
        author, 
        first_name_initial, 
        last_name,
        -- 按reference分组，按order_排序生成行号
        ROW_NUMBER() OVER (PARTITION BY reference ORDER BY order_) AS row_num
    FROM TableA
),
B_with_row AS (
    SELECT 
        reference, 
        first_name_initial, 
        last_name,
        -- 按reference分组，按原顺序生成行号（和Table A的顺序对应）
        ROW_NUMBER() OVER (PARTITION BY reference ORDER BY (SELECT NULL)) AS row_num
    FROM TableB
)
UPDATE A_with_row a
JOIN B_with_row b ON a.reference = b.reference AND a.row_num = b.row_num
SET 
    -- 拆分B的first_name_initial为A的首字母和姓氏
    a.first_name_initial = SUBSTRING_INDEX(b.first_name_initial, ' ', 1),
    a.last_name = SUBSTRING_INDEX(b.first_name_initial, ' ', -1);

关键说明： #

• PARTITION BY reference：把同个reference的记录分成一组
• ROW_NUMBER()：给每组内的记录按顺序编行号，确保A和B同组的第N条记录能匹配上
• SUBSTRING_INDEX：因为Table B的first_name_initial实际是全名（比如"H. Abbaszadeh"），所以用空格拆分出首字母部分和姓氏部分，对应Table A的两个字段

二、MySQL 5.x版本（不支持窗口函数） #

如果你的MySQL版本比较旧，就用用户变量来生成行号，通过临时表中转更新：

-- 步骤1：给Table A生成带行号的临时表
CREATE TEMPORARY TABLE A_temp AS
SELECT 
    reference, 
    author, 
    first_name_initial, 
    last_name,
    -- 用变量生成组内行号
    @row_num := IF(@current_ref = reference, @row_num + 1, 1) AS row_num,
    @current_ref := reference
FROM TableA, (SELECT @row_num := 0, @current_ref := '') AS vars
ORDER BY reference, order_;

-- 步骤2：给Table B生成带行号的临时表
CREATE TEMPORARY TABLE B_temp AS
SELECT 
    reference, 
    first_name_initial, 
    last_name,
    @row_num_b := IF(@current_ref_b = reference, @row_num_b + 1, 1) AS row_num,
    @current_ref_b := reference
FROM TableB, (SELECT @row_num_b := 0, @current_ref_b := '') AS vars
ORDER BY reference;

-- 步骤3：更新临时表中的字段
UPDATE A_temp a
JOIN B_temp b ON a.reference = b.reference AND a.row_num = b.row_num
SET 
    a.first_name_initial = SUBSTRING_INDEX(b.first_name_initial, ' ', 1),
    a.last_name = SUBSTRING_INDEX(b.first_name_initial, ' ', -1);

-- 步骤4：把更新后的数据同步回原Table A
UPDATE TableA a
JOIN A_temp temp ON a.reference = temp.reference AND a.author = temp.author
SET 
    a.first_name_initial = temp.first_name_initial,
    a.last_name = temp.last_name;

-- 可选：清理临时表
DROP TEMPORARY TABLE A_temp, B_temp;

关键说明： #

• 临时表用来存储带行号的中间结果，避免直接更新原表时出现的锁表或关联错误
• 用用户变量@row_num和@current_ref来模拟窗口函数的分组编号逻辑
• 最后通过reference+author（唯一标识Table A的记录）把临时表的更新同步回原表

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