Question 21756 in Mysql Question posted in
The official MySQL documentation can be found here.

How To Find Duplicate and Update Values in MySQL

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!

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0 
    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search