CODEWITHSUNDEEP
mysqldoc
yogesh
yogesh

Reputation: 1

How to change duplicate rows to unique values in mysql databse

I have a database table with about 1M records. I need to find all duplicate names in this table and make them unique.

Id Name

1 A 2 A 3 B 4 C 5 C Should be changed to...

Id Name

1 A 2 A-1 3 B 4 C 5 C-1 Is there an effective way of doing this with a mysql query or procedure?

Thanks in advance!

Upvotes: 0

Views: 224

Answers (1)

BVBAccelerate
BVBAccelerate

Reputation: 182

I needed to do something similar to a table I was just working on. I needed a unique URL field but the previous keepers of the data did not keep these constraints. The key was to create a temp table.

I used this response here to help: MySQL Error 1093 - Can't specify target table for update in FROM clause

Take note that it doesn't perform well, but then again if you only need to run it once on a database to clean a table then it shouldn't be so bad.

UPDATE `new_article` `upd`
SET `upd`.`url` = CONCAT(`upd`.`url`, '-', `upd`.`id`)
WHERE `upd`.`url` IN(
    SELECT `url` FROM (
        SELECT `sel`.`url` FROM `new_article` `sel`
        GROUP BY `sel`.`url` HAVING count(`sel`.`id`) > 1
    ) as `temp_table`
);

Upvotes: 1

Related Questions