如何使用Updatable CTE修改分组编号并同步更新两张关联表

方案1：数据库存储场景（适用百万级以上大数据量） #

核心逻辑是先生成旧NUM到新NUM的映射表，再用映射表同步更新两张表，保证数据一致性：

1. 生成NUM映射临时表

CREATE TEMPORARY TABLE num_mapping AS
SELECT 
  TYPE,
  Num AS old_num,
  CASE TYPE 
    WHEN 'LA' THEN 11 + ROW_NUMBER() OVER(PARTITION BY TYPE ORDER BY Num ASC)
    WHEN 'MB' THEN 19 + ROW_NUMBER() OVER(PARTITION BY TYPE ORDER BY Num ASC)
    -- 新增其他类型的起始规则直接加WHEN分支即可
  END AS new_num
FROM TableA;

2. 同步更新TableA的NUM字段

UPDATE TableA a
INNER JOIN num_mapping m 
  ON a.TYPE = m.TYPE AND a.Num = m.old_num
SET a.Num = m.new_num;

3. 同步更新TableB的NUM字段

UPDATE TableB b
INNER JOIN num_mapping m 
  ON b.TYPE = m.TYPE AND b.Num = m.old_num
SET b.Num = m.new_num;

方案2：文本/CSV文件场景（适用导出的静态数据处理） #

直接用Python脚本批量处理，配置好规则即可运行：

import csv
from collections import defaultdict

# 自定义配置：按需求修改各类型的NUM起始值
TYPE_START_CONFIG = {
    "LA": 12,
    "MB": 20
}
# 字段分隔符，根据实际文件格式修改，制表符填'\t'，逗号填','
FIELD_SEPARATOR = "-"

# 第一步：处理TableA，生成新旧NUM映射关系
num_mapping = defaultdict(dict)
type_current_counter = defaultdict(int)
new_table_a_rows = []

with open("你的TableA文件路径.csv", "r", encoding="utf-8") as f:
    reader = csv.reader(f, delimiter=FIELD_SEPARATOR)
    for row in reader:
        # 跳过空行、表头行
        if not row or len(row) < 2:
            new_table_a_rows.append(row)
            continue
        type_val = row[0].strip()
        old_num = int(row[1].strip())
        # 初始化当前类型的计数器
        if type_val not in type_current_counter:
            type_current_counter[type_val] = TYPE_START_CONFIG[type_val]
        new_num = type_current_counter[type_val]
        # 保存映射关系
        num_mapping[type_val][old_num] = new_num
        # 替换当前行的NUM
        row[1] = f" {new_num} "
        new_table_a_rows.append(row)
        # 计数器自增
        type_current_counter[type_val] += 1

# 写入处理后的TableA新文件
with open("TableA_处理后.csv", "w", encoding="utf-8", newline="") as f:
    writer = csv.writer(f, delimiter=FIELD_SEPARATOR)
    writer.writerows(new_table_a_rows)

# 第二步：用生成的映射关系处理TableB
new_table_b_rows = []
with open("你的TableB文件路径.csv", "r", encoding="utf-8") as f:
    reader = csv.reader(f, delimiter=FIELD_SEPARATOR)
    for row in reader:
        # 跳过空行、表头行
        if not row or len(row) < 3:
            new_table_b_rows.append(row)
            continue
        type_val = row[0].strip()
        old_num = int(row[1].strip())
        # 直接用已生成的映射替换NUM
        new_num = num_mapping[type_val][old_num]
        row[1] = f" {new_num} "
        new_table_b_rows.append(row)

# 写入处理后的TableB新文件
with open("TableB_处理后.csv", "w", encoding="utf-8", newline="") as f:
    writer = csv.writer(f, delimiter=FIELD_SEPARATOR)
    writer.writerows(new_table_b_rows)

注意事项 #

• 操作前务必备份原始数据，避免误操作导致数据丢失
• 如需新增其他TYPE的规则，直接在对应配置项中添加即可

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