使用此查询:
SELECT ARCustomerCode,
count(ARCustomerCode)
FROM customers
group by ARCustomerCode
having count(ARCustomerCode) > 1;
我能够确定ARCustomerCode不是唯一的行数.如何通过附加唯一的行ID来更新每个重复的ARCustomerCode字段?
欣赏你的洞察力!
使用此查询:
SELECT ARCustomerCode,
count(ARCustomerCode)
FROM customers
group by ARCustomerCode
having count(ARCustomerCode) > 1;
我能够确定ARCustomerCode不是唯一的行数.如何通过附加唯一的行ID来更新每个重复的ARCustomerCode字段?
欣赏你的洞察力!
您可以利用ROW_NUMBER
窗口函数 for each 重复的"ARCustomerCode"值分配一个排名,然后只更新每个代码的排名&>1(所有重复)的记录.
WITH cte AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ARCustomerCode ORDER BY <row_id_col>) AS rn
FROM customers
)
UPDATE customers
INNER JOIN cte
ON customers.<row_id_col> = cte.<row_id_col>
SET customers.ARCustomerCode = CONCAT(customers.ARCustomerCode, customers.<row_id_col>)
WHERE rn > 1