In MySQL, how to update id in a table if duplicates exist?-CodePudding

I want to update the id column in a table in MySQL using a query to append something in the value if exists already.

I ran the following query to find duplicates: select id, count(*) as count from temp_table group by id having count > 1;

Here are the duplicates:

id      count
5527    4

Current State:

Desired State:

CodePudding user response：

First u add new column as "Runid" with identity yes in that table, this column not affect your project, it just a unique id column, once u add with identity , u will get unique id for each row, based on Runid u can update your id column.

Update table tablename set id=100 where Runid=2

CodePudding user response：

To update reliably, we should add a unique id (like a primary key) so that the query knows which one of the duplicates it is updating.

alter table Tbl add UnqId int not null auto_increment primary key;

Then create a helper table (DupCnts) to count duplicates:

create table DupCnts 
select 
  UnqId, id, 
  CONCAT(id,
          coalesce(nullif (
                            (select count(*) as Cnt from Tbl b where a.id=b.id and a.UnqId>b.UnqId)
                           ,0)
                  ,'')
          ) as NewId
from 
   Tbl as a
;

Now you can updated the source table:

update Tbl as a
inner join DupCnts as b
on a.UnqId=b.UnqId
set a.id=b.NewId;

You can now drop the helper table. If you already have a primary key (you must!) you can use that instead of UnqId.