Mysql :: Delete Duplicate Records

# Delete Duplicate Tag


Topic Table :-
id primary key
topic_slug
topic_name

Tag Table:-
id primary key
tag_slug (not unique)
tag_name

Topic Tag Table (Relation table):-
id primary key
topic_id (Foreign key)
tag_id (Foreign key)
topic_id,tag_id key (not unique)

Due to non unique duplicate records occur , Now we have to remove duplicate records, So Mysql query :-

select min(id),group_concat(id) from tag t2 group by slug having count(slug) > 1;

create table temp_tag as select t1.id as t1_tag_id,t2.id as t2_tag_id FROM  tag t1, tag t2 WHERE t1.id < t2.id AND t1.slug = t2.slug;

update tag,temp_tag set tag_id = t1_tag_id where t2_tag_id = tag_id;

DELETE t2 FROM tag t1, tag t2 WHERE t1.id < t2.id AND t1.slug = t2.slug;

drop table temp_tag;