如何更新表中PEN_TYPE为PEN的DL_INC字段为同MEM_REF的PIP对应日期？

现有一张包含MEM_REF、PEN_TYPE、DL_INC字段的数据表，需要将所有PEN_TYPE为'PEN'的记录的DL_INC日期，替换为同一MEM_REF下PEN_TYPE为'PIP'的记录的DL_INC日期。

举个例子：MEM_REF为304852的PEN记录，DL_INC需从06/04/2020更新为11/06/2020（对应同MEM_REF的PIP记录日期）。

数据表示例：

尝试过MERGE和INSERT INTO语句但未成功，以下是可行的SQL解决方案：

解决方案 #

方法1：UPDATE + 关联子查询（通用写法） #

这种写法适配大多数关系型数据库（MySQL、SQL Server、Oracle等），同时确保只更新存在对应PIP记录的PEN行：

UPDATE 你的表名 t1
SET DL_INC = (
    SELECT DL_INC
    FROM 你的表名 t2
    WHERE t2.MEM_REF = t1.MEM_REF
      AND t2.PEN_TYPE = 'PIP'
)
WHERE t1.PEN_TYPE = 'PEN'
  AND EXISTS (
    SELECT 1
    FROM 你的表名 t2
    WHERE t2.MEM_REF = t1.MEM_REF
      AND t2.PEN_TYPE = 'PIP'
);

方法2：UPDATE + JOIN（性能更优） #

如果数据量较大，JOIN写法的性能通常优于子查询，分数据库给出写法：

MySQL

UPDATE 你的表名 t1
JOIN 你的表名 t2 
  ON t1.MEM_REF = t2.MEM_REF
  AND t2.PEN_TYPE = 'PIP'
SET t1.DL_INC = t2.DL_INC
WHERE t1.PEN_TYPE = 'PEN';

SQL Server

UPDATE t1
SET t1.DL_INC = t2.DL_INC
FROM 你的表名 t1
JOIN 你的表名 t2 
  ON t1.MEM_REF = t2.MEM_REF
  AND t2.PEN_TYPE = 'PIP'
WHERE t1.PEN_TYPE = 'PEN';

方法3：MERGE语句（修正版） #

如果之前MERGE失败，大概率是语法错误，以下是正确的MERGE实现：

Oracle

MERGE INTO 你的表名 t1
USING (
    SELECT MEM_REF, DL_INC
    FROM 你的表名
    WHERE PEN_TYPE = 'PIP'
) t2
ON (t1.MEM_REF = t2.MEM_REF AND t1.PEN_TYPE = 'PEN')
WHEN MATCHED THEN
    UPDATE SET t1.DL_INC = t2.DL_INC;

SQL Server

MERGE INTO 你的表名 t1
USING (
    SELECT MEM_REF, DL_INC
    FROM 你的表名
    WHERE PEN_TYPE = 'PIP'
) t2
ON t1.MEM_REF = t2.MEM_REF AND t1.PEN_TYPE = 'PEN'
WHEN MATCHED THEN
    UPDATE SET t1.DL_INC = t2.DL_INC;

注意事项 #

1. 替换所有语句中的你的表名为实际表名；
2. 执行更新前，建议先运行以下查询验证结果是否符合预期：

   SELECT 
       t1.MEM_REF, 
       t1.DL_INC AS 原日期, 
       t2.DL_INC AS 目标日期
   FROM 你的表名 t1
   JOIN 你的表名 t2 ON t1.MEM_REF = t2.MEM_REF
   WHERE t1.PEN_TYPE = 'PEN' AND t2.PEN_TYPE = 'PIP';
   

3. 若同一MEM_REF下存在多条PIP记录，需先明确取数规则（如最新日期），可在子查询中添加排序和限制：
   • MySQL：SELECT DL_INC FROM 你的表名 t2 WHERE ... ORDER BY DL_INC DESC LIMIT 1
   • SQL Server：SELECT TOP 1 DL_INC FROM 你的表名 t2 WHERE ... ORDER BY DL_INC DESC
   • Oracle：SELECT DL_INC FROM (SELECT DL_INC FROM 你的表名 t2 WHERE ... ORDER BY DL_INC DESC) WHERE ROWNUM = 1

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