Sunday, May 6, 2012

Mysql Delete Duplicates from a table

Let's say you have a table oldtable with the following fields:
 create table oldtable  
 (product_id int,  
 product_name varchar(100),  
 product_size varchar(100));  

Now you realize you have duplicate product_ids that may or may not have the same values for other fields. And you want to de-duplicate your table by picking only one of the rows.

You can do this is mysql very simply:

 create table newtable  
 select * from oldtable where 1 group by product_id  

This will give you unique product_ids with the product_name and product_size associated with the first occurrence of the product_id

No comments:

Post a Comment