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.
Empty the original table (you created a backup already right?).
Copy the ‘clean’ records back to the original table.
This can be used to normalize a table to add a primary key, data mining, cleaning a database or just for testing.
Popularity: 22%
Most Popular Posts
{ 7 comments… read them below or add one }
Its a good solution…for removing duplicates
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 )
Its always has been a trouble to keep up with the database expecially when they have duplicate rows. Nice explanation to solve it.
thinks for this tip
it’s really useful
think’s for sharing it
YOU ARE THE BEST!!!! I was looking for this code and i only obtain a full table delete.
I LOVE YOU!!!!
Another solution is we can add UNIQUE KEY for the field productid.
When we are adding UNIQUE KEY, all the duplicate records will be automatically deleted.
“ALTER IGNORE TABLE products_categories ADD UNIQUE KEY(productid)”
Useful solution here… How to delete duplicate rows with SQL
http://www.xaprb.com/blog/2006/10/11/how-to-delete-duplicate-rows-with-sql/