Oracle中如何通过SELECT关联实现按编码前缀匹配更新表VALUE字段

需求梳理 #

操作仅涉及表中CODE、VALUE两个字段：

• 表中CODE为预置编码，格式为[数字前缀]_A/[数字前缀]_B/[数字前缀]_C
• 需要将所有后缀为_C的编码对应的VALUE，替换为**相同数字前缀下后缀为_A**的编码对应的VALUE
• 不修改其他列、其他编码行的数据

原有语句问题 #

你最初写的语句存在核心逻辑漏洞：

update TABLE set Value = (select value from TABLE from CODE like '%A%') where CODE like '%C%'

内层子查询没有和外层待更新的_C记录做前缀关联，会返回所有带A的编码行，Oracle会直接抛出「单行子查询返回多行」的错误，即使强制执行也会把所有_C行的VALUE更新为同一个错误值，无法实现同前缀精准匹配。

可直接执行的正确写法 #

写法1：关联子查询（逻辑简单，适配小数据量表） #

和你原有写法逻辑最接近，核心是通过截取编码前缀做关联，保证X_A的值只会更新到同前缀的X_C：

UPDATE your_table t1
SET t1.VALUE = (
    SELECT t2.VALUE
    FROM your_table t2
    WHERE 
        -- 截取编码前缀（去掉最后两位的_A/_C后缀），保证前缀完全一致
        SUBSTR(t2.CODE, 1, LENGTH(t2.CODE) - 2) = SUBSTR(t1.CODE, 1, LENGTH(t1.CODE) - 2)
        AND t2.CODE LIKE '%\_A' ESCAPE '\' -- 精准匹配后缀为_A的行，转义下划线避免通配符误匹配
)
WHERE t1.CODE LIKE '%\_C' ESCAPE '\'; -- 仅更新后缀为_C的行

说明：SQL中_是LIKE语法的单字符通配符，加上ESCAPE '\'是为了匹配真实的下划线字符，避免出现类似1AC、XA这类不符合编码规则的行被误命中。如果你的编码完全符合规范、没有异常值，也可以省略转义直接写LIKE '%_A'。

写法2：MERGE更新（性能更优，适配大数据量表） #

MERGE是Oracle官方推荐的批量关联更新语法，执行效率比关联子查询更高，逻辑为先匹配同前缀的_A和_C行，匹配成功后再更新值：

MERGE INTO your_table t1
USING (
    SELECT 
        VALUE,
        SUBSTR(CODE, 1, LENGTH(CODE) - 2) AS code_prefix
    FROM your_table
    WHERE CODE LIKE '%\_A' ESCAPE '\'
) t2
ON (SUBSTR(t1.CODE, 1, LENGTH(t1.CODE) - 2) = t2.code_prefix)
WHEN MATCHED THEN
    UPDATE SET t1.VALUE = t2.VALUE
    WHERE t1.CODE LIKE '%\_C' ESCAPE '\';

更新前校验（必做） #

执行更新前先跑以下查询，确认匹配关系完全符合预期，避免误更新：

SELECT 
    t_c.CODE AS c_code,
    t_c.VALUE AS old_c_value,
    t_a.CODE AS matched_a_code,
    t_a.VALUE AS target_value
FROM your_table t_c
LEFT JOIN your_table t_a
ON SUBSTR(t_c.CODE, 1, LENGTH(t_c.CODE) - 2) = SUBSTR(t_a.CODE, 1, LENGTH(t_a.CODE) - 2)
AND t_a.CODE LIKE '%\_A' ESCAPE '\'
WHERE t_c.CODE LIKE '%\_C' ESCAPE '\';

查询结果中每一条_C编码对应的matched_a_code都应该是同前缀的_A编码（比如1_C对应1_A、3_C对应3_A），target_value就是要更新的目标值，确认无误后再执行更新语句即可。

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