Databases (73)
InterBase (29)
MS-SQL (5)
mysql (37)
Oracle (1)
Linux (41)
Outside the Cube (5088)
Programming (679)
Web publishing (65)
Windows (431)
Exchange Links
About this site
| |
 |
Remove duplicate rows from a table - independent from indexes
This article has not been rated yet. After reading, feel free to leave comments and rate it.
Question:
I need to remove duplicate rows from my table. It has an autoincrement integer as primary key and a few other attributes (PROD_ID, RESOURCE_ID, DATE_ADDED). What's the easiest way to do that?Answer:
Follow these steps:- Create a temporary second table with an identical structure to your table. Run a select statement with the keyword DISTINCT on the desired attributes. That means, leave out the attributes that do not make a row 'unique'.
- If you have cascading deletes, constraints or (on delete) triggers defined on your table, then temporarily disable them.
- Now empty your table.
- Then insert from your temporary table into your original.
- Enable your triggers or constraints again.
 |  | |
/* the original table */ create table product_to_resources ( pr_id int autoincrement; /* primary key */ prod_id int; resource_id int; date_added datetime; ); /* the temporary table */ create table TMP_product_to_resources ( pr_id int autoincrement; prod_id int; resource_id int; date_added datetime; ); /* get only unique rows */ delete from TMP_product_to_resources; insert into TMP_product_to_resources (prod_id, resource_id, date_added) select distinct prod_id, resource_id, date_added from product_to_resources; /* now disable your triggers, constraints.. */ delete from product_to_resources; /* insert them back */ insert into product_to_resources (prod_id, resource_id, date_added) select distinct prod_id, resource_id, date_added from TMP_product_to_resources; /* now enable your triggers, constraints again. Done. */ | ||
 |  |
|
||||||||
Comments: