Skip to content


Delete Duplicate Rows in MySQL

1.5 million rows in a products_categories table. That’s 1,500,000 products to categories relations. I needed a quick way to remove products from multiple categories to test the performance hit of the HUGE products_categories table. A quick and dirty is to remove duplicate productids from the table. Here’s a quick howto.

Create a temporary table with only one row for each productid (the GROUP BY clause). Change this GROUP BY to be the field with duplicates to remove. You can also add some logic here to select which records to keep.

CREATE TABLE products_categories_temp AS SELECT * FROM products_categories GROUP BY productid

Empty the original table (you created a backup already right?).

TRUNCATE products_categories

Copy the ‘clean’ records back to the original table.

INSERT INTO products_categories SELECT * FROM products_categories_temp

This can be used to normalize a table to add a primary key, data mining, cleaning a database or just for testing.

Popularity: 28% [?]

Posted in Snippets.


4 Responses

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.

  1. Arpit says

    Its a good solution…for removing duplicates

  2. viral says

    other simple way is

    create table temp as select * from t1 group by fields;

    delete from t1 where id not in (select id from temp )

  3. Agnel says

    Its always has been a trouble to keep up with the database expecially when they have duplicate rows. Nice explanation to solve it.

  4. m4 says

    thinks for this tip
    it’s really useful
    think’s for sharing it



Some HTML is OK

or, reply to this post via trackback.