编写存储过程：按TransId批量更新TableA.DocNum为TableB.DocNum+1

需求说明 #

现有两张表：

• TableA：通过TransId分组，审核完成后，每组所有记录需分配TableB初始DocNum+递增序号的DocNum，每个TransId对应一次递增
• TableB：需更新为最终使用的最大DocNum值

初始表结构：

TableA初始数据 #

TableB初始数据 #

预期结果：

TableA更新后 #

TableB更新后 #

解决方案（以MySQL为例） #

无需用循环，通过窗口函数+批量更新即可实现：

完整可执行SQL #

-- 生成TransId与新DocNum的映射表，同时更新TableA
WITH trans_doc_map AS (
    SELECT 
        t.TransId,
        LPAD(CAST((SELECT CAST(DocNum AS UNSIGNED) FROM TableB) + ROW_NUMBER() OVER (ORDER BY t.TransId) AS CHAR), 6, '0') AS new_doc_num
    FROM (SELECT DISTINCT TransId FROM TableA) t
)
UPDATE TableA a
JOIN trans_doc_map m ON a.TransId = m.TransId
SET a.DocNum = m.new_doc_num;

-- 更新TableB为最终使用的最大DocNum
WITH trans_doc_map AS (
    SELECT 
        LPAD(CAST((SELECT CAST(DocNum AS UNSIGNED) FROM TableB) + ROW_NUMBER() OVER (ORDER BY t.TransId) AS CHAR), 6, '0') AS new_doc_num
    FROM (SELECT DISTINCT TransId FROM TableA) t
)
UPDATE TableB b
SET b.DocNum = (SELECT MAX(new_doc_num) FROM trans_doc_map);

关键逻辑说明 #

1. 用ROW_NUMBER()窗口函数给每个唯一TransId分配递增序号，替代循环实现高效分组递增
2. LPAD()函数确保DocNum始终为6位带前导零的字符串，保持格式一致
3. 先通过临时映射表建立TransId与新DocNum的对应关系，再批量更新TableA，最后同步更新TableB的最大值

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