同一查询中结合INSERT与UPDATE实现折扣码分配的存储过程编写

• 操作逻辑错位：待分配折扣码的客户在CUSTOMER_DATABASE中已经存在，仅CUSTOMER_CODE字段为空，正确操作应该是更新现有客户记录的空字段，原写法的INSERT逻辑会直接造成客户数据重复
• 数据映射缺失：没有建立「待分配客户」和「可用折扣码」的一一对应关系，原INSERT直接把所有空状态的折扣码捞取出来，完全没有匹配到具体客户
• 过滤条件遗漏：原UPDATE语句没有任何匹配过滤条件，会把全表所有空状态的折扣码都标记为USED，和前面的INSERT操作没有任何数据关联，最终就会出现折扣码全被标记已用、但客户没拿到对应折扣码的状态不一致问题

要满足分配规则，必须落地三个硬性要求：

• 仅筛选CUSTOMER_CODE为空的存量客户作为分配池，不新增重复客户记录
• 仅筛选STATUS为空的未使用折扣码作为可用池，每个折扣码仅和一个客户绑定，不重复分配
• 整个分配操作必须原子提交，不能出现客户拿到折扣码但折扣码没被标记为已用、或折扣码被标记为已用但客户没拿到码的中间状态

完全可以在同一个事务逻辑块里完成分配和状态更新操作，不需要拆分多次独立执行：

-- 开启事务保证操作原子性
BEGIN TRANSACTION;

WITH customer_pool AS (
    -- 筛选所有待分配折扣码的客户，按客户ID排序保证映射顺序稳定
    SELECT CUSTOMER_ID, ROW_NUMBER() OVER(ORDER BY CUSTOMER_ID) AS rn
    FROM CUSTOMER_DATABASE
    WHERE CUSTOMER_CODE IS NULL OR CUSTOMER_CODE = ''
),
discount_pool AS (
    -- 筛选所有可用的未使用折扣码，按折扣码ID排序保证映射顺序稳定
    SELECT ID, CODE, ROW_NUMBER() OVER(ORDER BY ID) AS rn
    FROM DATA_DISCOUNT_CODES
    WHERE STATUS IS NULL OR STATUS = ''
),
matched_result AS (
    -- 两个池按行号一一匹配，仅取能对应上的记录，避免客户和折扣码数量不对等时的错位问题
    SELECT c.CUSTOMER_ID, d.ID AS discount_id, d.CODE
    FROM customer_pool c
    INNER JOIN discount_pool d ON c.rn = d.rn
)

-- 第一步：将匹配好的折扣码更新到对应客户的空CUSTOMER_CODE字段
UPDATE c
SET c.CUSTOMER_CODE = mr.CODE
FROM CUSTOMER_DATABASE c
INNER JOIN matched_result mr ON c.CUSTOMER_ID = mr.CUSTOMER_ID;

-- 第二步：将已匹配的折扣码状态更新为USED
UPDATE d
SET d.STATUS = 'USED'
FROM DATA_DISCOUNT_CODES d
INNER JOIN matched_result mr ON d.ID = mr.discount_id;

-- 校验逻辑无异常后提交事务，出现异常直接回滚
COMMIT TRANSACTION;

上述逻辑并非将两个无关联的语句硬拼接，而是通过公共表表达式（CTE）提前把待分配客户和可用折扣码按稳定规则一一映射，后续的两个更新操作都基于这个固定映射结果执行，既保证客户拿到正确的折扣码，也保证对应折扣码的状态被同步更新。
注意：不同数据库的关联更新语法略有差异，比如MySQL需要调整UPDATE的关联写法，Oracle需要改用MERGE语句实现，但核心的匹配、映射、原子提交逻辑是完全通用的。

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