How To Find Duplicate and Update Values in MySQL-CodePudding

using this query:

SELECT ARCustomerCode,
       count(ARCustomerCode)
FROM customers
group by ARCustomerCode
having count(ARCustomerCode) > 1;

I am able to identify the number of rows where ARCustomerCode is not unique. How can I update each duplicate ARCustomerCode field by appending the unique row id?

Appreciate the insight!

CodePudding user response：

You can exploit the ROW_NUMBER window function to assign a ranking to each duplicate of "ARCustomerCode" values, then update only the records which have ranking > 1 (all duplicates) for each code.

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

CodePudding user response：

select case 
  when lag(ARCustomerCode) over (order by ARCustomerCode) = ARCustomerCode
    then code   cast(row_number() over (partition by ARCustomerCodeorder by ARCustomerCode) as varchar(50))
  when lead(ARCustomerCode) over (order by ARCustomerCode) = ARCustomerCode
    then code   cast(row_number() over (partition by ARCustomerCodeorder by ARCustomerCode) as varchar(50))
  else ARCustomerCode 
end

It's even simpler if you don't need to append a number to the first instance of the dup.

select case 
  when lag(ARCustomerCode) over (order by ARCustomerCode) = ARCustomerCode
    then code   cast(row_number() over (partition by ARCustomerCodeorder by ARCustomerCode) as varchar(50))
  else ARCustomerCode 
end