SQL自连接实现同表不同来源行Postcode字段值批量复制

完全可以通过表自连接实现需求，执行效率远高于你当前用的条件插入方案，千级数据量下无需额外优化也能毫秒级完成。

匹配逻辑修正 #

你最初想仅用StreetNo关联的思路存在错配风险：同一城市下不同街道可能存在相同门牌号，关联条件需要叠加规则避免错配：

• City字段完全等值匹配
• StreetNo字段完全等值匹配
• StreetName做模糊匹配适配前缀差异：你的样例中Y源街道名仅比X源多了"Calle"前缀，只要判定Y源的StreetName包含X源的StreetName，即可认定为同一条街道

邮编提取规则 #

从样例数据看，Y源Postcode中6字符额外邮编固定为字段中第一个逗号前的内容，直接用字符串截取函数提取即可，不需要复杂正则处理。

可直接执行的SQL代码 #

执行更新前务必先跑验证查询，确认匹配关系符合预期，避免脏数据

首先执行匹配验证查询：

SELECT 
  a_x.StreetName x_street,
  a_x.StreetNo x_no,
  a_x.Postcode x_old_postcode,
  a_y.StreetName y_street,
  SUBSTRING_INDEX(a_y.Postcode, ',', 1) y_extra_postcode
FROM Address a_x
INNER JOIN Address a_y
  ON a_x.Source = 'X'
  AND a_y.Source = 'Y'
  AND a_x.City = a_y.City
  AND a_x.StreetNo = a_y.StreetNo
  AND a_y.StreetName LIKE CONCAT('%', a_x.StreetName);

确认返回的配对关系、待补充的6位邮编都正确后，执行更新语句：

UPDATE Address a_x
INNER JOIN Address a_y
  ON a_x.Source = 'X'
  AND a_y.Source = 'Y'
  AND a_x.City = a_y.City
  AND a_x.StreetNo = a_y.StreetNo
  AND a_y.StreetName LIKE CONCAT('%', a_x.StreetName)
SET a_x.Postcode = CONCAT(
  SUBSTRING_INDEX(a_y.Postcode, ',', 1),
  ', ',
  a_x.Postcode
);

优化建议 #

• 如果后续遇到街道名差异不只是前缀的场景，可以把街名匹配规则改成双向包含：a_y.StreetName LIKE CONCAT('%', a_x.StreetName) OR a_x.StreetName LIKE CONCAT('%', a_y.StreetName)，覆盖前后缀差异、冗余字符等常见不一致问题
• 数据量增长到十万级以上时，可以给(Source, City, StreetNo)建联合索引，关联查询效率会提升一个量级
• 你之前用条件插入的逻辑不符合需求：目标是更新已有X源记录的字段值，而非插入新行，冗余数据插入也是你之前方案执行效率差的核心原因之一

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