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

Question

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:

you are updating the duplicate IDs by adding a sequential number to them? that is easy but how would you gurantee that the generated number will not clash with another existing ID? Also, are you holding these in an integer column or varchar? — tinazmu, Aug 04 '22 at 02:41
The column is Integer. Sequential order is not necessary. It could be a random number as well but only the next occurrences should be updated while the 1st occurrence should remain the same. — Abdullah Khawer, Aug 04 '22 at 02:47
Understand, but you don't specify how clashes are to be handled. For your example, if the table already had 55272, what would you do? try the next number, what is that exists also? — tinazmu, Aug 04 '22 at 02:51
Instead of 55272, keep 5527 then make it 55272222, then 55273333, that user id is really big and we are not going to have that much users in the future, if happened, we will update the query to start making it 552722222 instead. — Abdullah Khawer, Aug 04 '22 at 03:05
If there is a better solution, I'm fine with that as well, we just want to have a different value for the id column in case of duplicates. — Abdullah Khawer, Aug 04 '22 at 03:05
See [delete duplicates](https://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql), make `id` a primary or unique key. Then use [insert ... on duplicate key update](https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html). — danblack, Aug 04 '22 at 03:32

tinazmu · Accepted Answer · 2022-08-04T04:18:59.493

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.

score 0 · Answer 2 · answered Aug 04 '22 at 03:32

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

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

2 Answers2