SQL存储过程实现跨表编码匹配更新oCode布尔标志位

• oCode表：存储2个核心字段，分别是固定15位长度的编码字段、布尔类型的标志位字段
• pCode表：存储1个核心字段，为一系列固定9位长度的编码值

提取oCode表每条记录15位编码的前9位，若该9位值在pCode表的9位编码集合中存在匹配项，就将oCode表对应记录的布尔标志位设为true。

以下为不同主流数据库的对应实现，根据实际使用的数据库选型，将代码中的字段占位符替换为业务实际字段名即可：

MySQL 实现 #

UPDATE oCode
INNER JOIN pCode 
  ON LEFT(oCode.15位编码字段名, 9) = pCode.9位编码字段名
SET oCode.布尔标志位字段名 = TRUE;

如果需要避免重复更新已经是true的记录，可以在末尾追加WHERE条件 WHERE oCode.布尔标志位字段名 = FALSE，减少不必要的行锁开销

SQL Server 实现 #

UPDATE o
SET o.布尔标志位字段名 = 1
FROM oCode o
INNER JOIN pCode p 
  ON LEFT(o.15位编码字段名, 9) = p.9位编码字段名;

Oracle / PostgreSQL 实现 #

UPDATE oCode o
SET o.布尔标志位字段名 = TRUE
WHERE EXISTS (
  SELECT 1 
  FROM pCode p 
  WHERE p.9位编码字段名 = SUBSTR(o.15位编码字段名, 1, 9)
);

• 编码字段存在首尾空格脏数据：匹配前对两个表的编码字段加TRIM()处理，避免不可见空格导致匹配失效
• 字符串截取函数参数错误：所有主流数据库截取字符串前N位的逻辑，起始下标均为1，不要传0导致截取结果错位
• 误用LIKE模糊匹配：不要用o.15位编码字段名 LIKE CONCAT(p.9位编码字段名, '%')的写法，在编码存在特殊字符、转义符时会出现误匹配
• 字段类型不匹配：确保两个编码字段的字符集、排序规则一致，避免隐式类型转换导致匹配失败

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