如何通过PL/SQL或Unix脚本逐行更新表中指定列的最佳匹配值

PL/SQL实现（Oracle环境） #

先做字段名约定，你可以替换为实际业务使用的字段名：

• table1第三列：col3（字符串类型）
• table1第十二列：col12
• table2第一列：match_code（字符串类型）
• table2第四列：country

推荐使用MERGE语句实现，无需显式写循环，执行效率更高：

MERGE INTO table1 t1
USING (
    SELECT 
        t1.rowid AS rid,
        t2.country
    FROM table1 t1
    LEFT JOIN LATERAL (
        SELECT country
        FROM table2 t2
        WHERE t2.match_code = SUBSTR(t1.col3, 1, LENGTH(t1.col3) - LEVEL + 1)
        CONNECT BY LEVEL <= LENGTH(t1.col3)
        ORDER BY LEVEL ASC
        FETCH FIRST 1 ROW ONLY
    ) t2 ON 1=1
) res
ON (t1.rowid = res.rid)
WHEN MATCHED THEN UPDATE SET t1.col12 = res.country;

如果是不支持LATERAL语法的老版本Oracle，也可以用存储过程实现：

DECLARE
    CURSOR cur_t1 IS SELECT rowid, col3 FROM table1 FOR UPDATE;
    v_col3 table1.col3%TYPE;
    v_rid ROWID;
    v_country table2.country%TYPE;
    v_len NUMBER;
BEGIN
    OPEN cur_t1;
    LOOP
        FETCH cur_t1 INTO v_rid, v_col3;
        EXIT WHEN cur_t1%NOTFOUND;
        v_len := LENGTH(v_col3);
        v_country := NULL;
        -- 逐位截断匹配
        FOR i IN REVERSE 1..v_len LOOP
            BEGIN
                SELECT country INTO v_country 
                FROM table2 
                WHERE match_code = SUBSTR(v_col3, 1, i)
                AND ROWNUM = 1;
                EXIT WHEN v_country IS NOT NULL;
            EXCEPTION
                WHEN NO_DATA_FOUND THEN NULL;
            END;
        END LOOP;
        -- 更新字段
        UPDATE table1 SET col12 = v_country WHERE rowid = v_rid;
    END LOOP;
    CLOSE cur_t1;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

Unix Shell实现（基于awk） #

该方案适用于已将两张表导出为逗号分隔CSV文件的场景，假设table1导出为table1.csv，table2导出为table2.csv，字段顺序和样例完全一致。

新建匹配脚本match.awk，内容如下：

BEGIN {
    FS = ","
    OFS = ","
}
# 读取table2加载匹配规则
NR == FNR {
    match_map[$1] = $4
    next
}
# 处理table1每一行
{
    col3 = $3
    len = length(col3)
    replace_val = ""
    # 逐位截断匹配
    for (i = len; i >= 1; i--) {
        check_key = substr(col3, 1, i)
        if (check_key in match_map) {
            replace_val = match_map[check_key]
            break
        }
    }
    # 替换第12列输出
    if (replace_val != "") {
        $12 = replace_val
    }
    print
}

执行命令生成更新后的文件：

awk -f match.awk table2.csv table1.csv > updated_table1.csv

运行后updated_table1.csv就是符合要求的结果文件。

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