基于同表数据实现ICUNIT表CT单位CONVERSION的新增或更新操作

前提说明 #

现有ICUNIT表，核心字段为ITEMNO（物料编号）、UNIT（单位）、CONVERSION（转换系数），数据样例如下：

+--------+------+------------+
| ITEMNO | UNIT | CONVERSION |
+--------+------+------------+
| 123    | CTN  | 50         |
+--------+------+------------+
| 456    | CTN  | 300        |
+--------+------+------------+
| 789    | CTN  | 200        |
+--------+------+------------+

需实现逻辑 #

• 为每个存在UNIT='CTN'记录的ITEMNO新增UNIT='CT'的记录，CONVERSION值与同物料CTN单位的转换系数一致
• 若对应ITEMNO已存在UNIT='CT'的记录，直接将该记录的CONVERSION更新为同物料CTN单位的转换系数
• 仅处理CT、CTN两种单位，其余单位数据不受影响

原SQL错误原因 #

原执行失败的UPDATE语句子查询未关联外层的ITEMNO字段，会返回多条CTN单位的CONVERSION值，触发多行赋值报错，同时也没有覆盖新增CT记录的场景。

可行实现方案 #

方案1：通用两步法（兼容所有主流数据库） #

无需数据库特定语法，适配MySQL、PostgreSQL、Oracle、SQL Server等所有主流数据库

第一步：更新已存在的CT记录

UPDATE ICUNIT t1
SET t1.CONVERSION = (
    SELECT t2.CONVERSION 
    FROM ICUNIT t2 
    WHERE t2.ITEMNO = t1.ITEMNO 
      AND t2.UNIT = 'CTN'
)
WHERE t1.UNIT = 'CT'
  AND EXISTS (
    SELECT 1 
    FROM ICUNIT t3 
    WHERE t3.ITEMNO = t1.ITEMNO 
      AND t3.UNIT = 'CTN'
);

第二步：插入不存在的CT记录

INSERT INTO ICUNIT (ITEMNO, UNIT, CONVERSION)
SELECT t.ITEMNO, 'CT' AS UNIT, t.CONVERSION
FROM ICUNIT t
WHERE t.UNIT = 'CTN'
  AND NOT EXISTS (
    SELECT 1 
    FROM ICUNIT t4 
    WHERE t4.ITEMNO = t.ITEMNO 
      AND t4.UNIT = 'CT'
);

方案2：单语句UPSERT实现（需数据库适配） #

要求提前给(ITEMNO, UNIT)组合字段添加唯一约束/索引

MySQL 8.0+/MariaDB版本

INSERT INTO ICUNIT (ITEMNO, UNIT, CONVERSION)
SELECT ITEMNO, 'CT', CONVERSION
FROM ICUNIT
WHERE UNIT = 'CTN'
ON DUPLICATE KEY UPDATE CONVERSION = VALUES(CONVERSION);

PostgreSQL版本

INSERT INTO ICUNIT (ITEMNO, UNIT, CONVERSION)
SELECT ITEMNO, 'CT', CONVERSION
FROM ICUNIT
WHERE UNIT = 'CTN'
ON CONFLICT (ITEMNO, UNIT) 
DO UPDATE SET CONVERSION = EXCLUDED.CONVERSION;

Oracle版本

MERGE INTO ICUNIT t1
USING (
    SELECT ITEMNO, CONVERSION 
    FROM ICUNIT 
    WHERE UNIT = 'CTN'
) t2
ON (t1.ITEMNO = t2.ITEMNO AND t1.UNIT = 'CT')
WHEN MATCHED THEN 
    UPDATE SET t1.CONVERSION = t2.CONVERSION
WHEN NOT MATCHED THEN 
    INSERT (ITEMNO, UNIT, CONVERSION)
    VALUES (t2.ITEMNO, 'CT', t2.CONVERSION);

注意事项 #

• 执行前建议先备份表数据，或先执行SELECT子句验证匹配结果符合预期
• 建议给ICUNIT表的(ITEMNO, UNIT)组合字段添加唯一索引，避免出现同物料同单位的重复脏数据

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