如何实现单表更新：将同ID下PG2的Price赋值给PG1的空Price行

核心逻辑是通过自关联匹配同一ID下PriceGroup为PG2的有效价格，再批量更新符合条件的PG1行的空值Price字段，不同数据库的写法如下：

MySQL #

UPDATE PriceTable t1
INNER JOIN PriceTable t2 
  ON t1.ID = t2.ID 
  AND t2.PriceGroup = 'PG2' 
  AND t2.Price IS NOT NULL
SET t1.Price = t2.Price
WHERE t1.PriceGroup = 'PG1' 
  AND t1.Price IS NULL;

PostgreSQL #

UPDATE PriceTable t1
SET Price = t2.Price
FROM PriceTable t2
WHERE t1.ID = t2.ID
  AND t1.PriceGroup = 'PG1'
  AND t1.Price IS NULL
  AND t2.PriceGroup = 'PG2'
  AND t2.Price IS NOT NULL;

SQL Server #

UPDATE t1
SET t1.Price = t2.Price
FROM PriceTable t1
INNER JOIN PriceTable t2
  ON t1.ID = t2.ID
  AND t2.PriceGroup = 'PG2'
  AND t2.Price IS NOT NULL
WHERE t1.PriceGroup = 'PG1'
  AND t1.Price IS NULL;

Oracle #

UPDATE PriceTable t1
SET t1.Price = (
  SELECT t2.Price
  FROM PriceTable t2
  WHERE t2.ID = t1.ID
    AND t2.PriceGroup = 'PG2'
    AND t2.Price IS NOT NULL
)
WHERE t1.PriceGroup = 'PG1'
  AND t1.Price IS NULL
  AND EXISTS (
    SELECT 1
    FROM PriceTable t2
    WHERE t2.ID = t1.ID
      AND t2.PriceGroup = 'PG2'
      AND t2.Price IS NOT NULL
);

注意事项 #

• 执行更新操作前建议先执行验证查询确认匹配结果正确，避免误改数据：

-- 验证查询通用示例
SELECT t1.ID, t1.Price AS PG1当前价格, t2.Price AS PG2对应价格
FROM PriceTable t1
JOIN PriceTable t2 ON t1.ID = t2.ID
WHERE t1.PriceGroup = 'PG1' AND t1.Price IS NULL
AND t2.PriceGroup = 'PG2' AND t2.Price IS NOT NULL;

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