跨库表更新：基于callsign取源表最大fccid更新目标表字段

需要为现有目标表stations填充/更新fccid, city, state, zip字段，数据源来自另一数据库的源表fcc_amateur.en（140万+条记录），目标表约9000条记录。具体要求如下：

• 两表通过callsign字段关联
• 源表中单个callsign对应多条记录，需取该callsign对应的**最大fccid**所在记录的字段值来更新目标表
• 已写出提取更新数据的查询语句，但不清楚如何直接用该数据更新目标表，是否需要先将结果存入临时文件？直接更新的操作方法是什么？

原提取数据查询代码 #

SELECT b.callsign, a.fccid, a.city, a.state, a.zip
  FROM fcc_amateur.en  a
 INNER JOIN (
    SELECT callsign, MAX(fccid) fccid
      FROM fcc_amateur.en
      GROUP BY callsign ) b
               ON a.callsign = b.callsign AND a.fccid = b.fccid
;

待完善的原更新代码 #

UPDATE stations SET 
        fccid = b.fccid,
        city = a.city,  state = a.state, zip = a.zip
  WHERE a.callsign = b.callsign
;

不需要将查询结果存入临时文件，直接通过UPDATE ... JOIN语法即可完成关联更新，将你的数据提取逻辑整合到更新语句中即可。

完整更新代码 #

UPDATE stations s
JOIN (
    -- 复用你编写的获取每个callsign最新记录的子查询
    SELECT b.callsign, a.fccid, a.city, a.state, a.zip
    FROM fcc_amateur.en a
    INNER JOIN (
        SELECT callsign, MAX(fccid) fccid
        FROM fcc_amateur.en
        GROUP BY callsign
    ) b ON a.callsign = b.callsign AND a.fccid = b.fccid
) src ON s.callsign = src.callsign
SET
    s.fccid = src.fccid,
    s.city = src.city,
    s.state = src.state,
    s.zip = src.zip;

代码说明 #

1. 为目标表stations起别名s，为子查询结果起别名src，简化语句书写并避免字段歧义
2. 通过JOIN将目标表与子查询得到的源表最新记录数据集关联，关联条件为callsign字段匹配
3. SET子句中直接将目标表的对应字段赋值为src数据集中的字段值
4. 该语句会自动为目标表中每个callsign匹配源表中对应最大fccid的记录，完成批量更新

操作建议 #

• 执行更新前，先单独运行子查询（即你原有的SELECT语句），确认返回的记录和字段值符合预期，避免误更新
• 若你的数据库支持事务，建议在更新前开启事务，更新完成后验证数据无误再提交，以便在出现错误时可以回滚
• 如果目标表中存在callsign在源表中无匹配的记录，这类记录不会被更新，保持原有值

内容的提问来源于stack exchange，提问作者Keith D Kaiser