{"id":1860,"date":"2013-05-31T13:28:59","date_gmt":"2013-05-31T13:28:59","guid":{"rendered":"http:\/\/www.withdata.com\/ad\/?p=1860"},"modified":"2017-04-20T11:03:42","modified_gmt":"2017-04-20T11:03:42","slug":"remove-duplicate-rows-in-mysql","status":"publish","type":"post","link":"https:\/\/www.withdata.com\/blog\/mysql\/remove-duplicate-rows-in-mysql.html","title":{"rendered":"Remove duplicate rows in MySQL"},"content":{"rendered":"

The primary key: id, the unique columns: \u00a0col_1, col_2, col_3 .<\/p>\n

You can use a temporary table, like:<\/p>\n

create temporary table temp_table (id int);\r\n\r\ninsert  temp_table\r\n        (id)\r\nselect  id\r\nfrom    your_table t1\r\nwhere   exists\r\n        (\r\n        select  *\r\n        from    your_table t2\r\n        where   t2.col_1 = t1.col_1\r\n                and t2.col_2 = t1.col_2\r\n                and t2.col_3 = t1.col_3\r\n                and t2.id > t1.id\r\n        );\r\n\r\ndelete\r\nfrom    your_table\r\nwhere   id in (select id from temp_table);<\/pre>\n
Or you can add a\u00a0UNIQUE index on the 3 columns. When you write the<\/p>\n
ALTER IGNORE TABLE your_table ADD UNIQUE INDEX idx_name (col_1, col_2, col_3);<\/pre>\n
This will drop all the duplicate rows. As an added benefit, future\u00a0INSERTs\u00a0that are duplicates will error out. And you’d better take a backup before running this statement.<\/p>\n
Relative links:<\/p>\n
Remove duplicate rows in SQL Server<\/a><\/h2>\n
Remove duplicate rows in Oracle<\/a><\/h2>\n
 <\/p>\n
Some MySQL tools you can try:  https:\/\/www.withdata.com\/mysql\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
The primary key: id, the unique columns: \u00a0col_1, col_2, col_3 . You can use a temporary table, like: create temporary table temp_table (id int); insert temp_table (id) select id from your_table t1 where exists ( select * from your_table t2 where t2.col_1 = t1.col_1 and t2.col_2 = t1.col_2 and t2.col_3 = t1.col_3 and t2.id > […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29],"tags":[1323,234],"class_list":["post-1860","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-mysql","tag-remove-duplicate-rows"],"_links":{"self":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/1860","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/comments?post=1860"}],"version-history":[{"count":7,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/1860\/revisions"}],"predecessor-version":[{"id":6447,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/1860\/revisions\/6447"}],"wp:attachment":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/media?parent=1860"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/categories?post=1860"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/tags?post=1860"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}