如何更新Locality-market表实现每个locality_id对应唯一locality_name

问题原因 #

你原来的SQL是逐行将空的locality_name替换为当前行的market_name，当同一个locality_id对应多条market_name不同的记录时，自然会出现同一个locality_id关联多个locality_name的问题。

实现规则 #

根据你给出的预期结果，统一更新规则为：同一个locality_id下所有空的locality_name，统一赋值为该locality_id分组内market_id最小的记录对应的market_name，完全匹配你给出的示例效果。

具体SQL写法 #

MySQL 8.0+

UPDATE `Locality-market` lm
JOIN (
  SELECT 
    Locality_id,
    market_name AS assign_name
  FROM (
    SELECT 
      Locality_id,
      market_name,
      ROW_NUMBER() OVER (PARTITION BY Locality_id ORDER BY market_id ASC) AS rn
    FROM `Locality-market`
  ) t
  WHERE rn = 1
) ref ON lm.Locality_id = ref.Locality_id
SET lm.locality_name = ref.assign_name
WHERE lm.locality_name = '' OR lm.locality_name IS NULL;

MySQL 5.x（不支持窗口函数）

UPDATE `Locality-market` lm
JOIN (
  SELECT 
    t1.Locality_id,
    t1.market_name AS assign_name
  FROM `Locality-market` t1
  JOIN (
    SELECT Locality_id, MIN(market_id) AS min_mid
    FROM `Locality-market`
    GROUP BY Locality_id
  ) t2 ON t1.Locality_id = t2.Locality_id AND t1.market_id = t2.min_mid
) ref ON lm.Locality_id = ref.Locality_id
SET lm.locality_name = ref.assign_name
WHERE lm.locality_name = '' OR lm.locality_name IS NULL;

PostgreSQL

UPDATE "Locality-market" lm
SET locality_name = ref.assign_name
FROM (
  SELECT 
    Locality_id,
    market_name AS assign_name
  FROM (
    SELECT 
      Locality_id,
      market_name,
      ROW_NUMBER() OVER (PARTITION BY Locality_id ORDER BY market_id ASC) AS rn
    FROM "Locality-market"
  ) t
  WHERE rn = 1
) ref
WHERE lm.Locality_id = ref.Locality_id
AND (lm.locality_name = '' OR lm.locality_name IS NULL);

SQL Server

WITH ref AS (
  SELECT 
    Locality_id,
    market_name AS assign_name,
    ROW_NUMBER() OVER (PARTITION BY Locality_id ORDER BY market_id ASC) AS rn
  FROM [Locality-market]
)
UPDATE lm
SET lm.locality_name = r.assign_name
FROM [Locality-market] lm
JOIN ref r ON lm.Locality_id = r.Locality_id AND r.rn = 1
WHERE lm.locality_name = '' OR lm.locality_name IS NULL;

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