DelphiFAQ Home Search:
General :: Databases
Database installation, programming and administration.

Articles:

This list is sorted by recent document popularity (not total page views).
New documents will first appear at the bottom.

Featured Article

Remove duplicate rows from a table - independent from indexes

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:
  1. 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'.
  2. If you have cascading deletes, constraints or (on delete) triggers defined on your table, then temporarily disable them.
  3. Now empty your table.
  4. Then insert from your temporary table into your original.
  5. 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. */
 

Generated 12:00:37 on May 24, 2017