DelphiFAQ Home Search:

InterBase, MS-SQL, mysql, Oracle
C#, C++, Delphi, Java,
JavaScript, perl, php, Visual Basic, VBScript
Apache, Network, Shell
Web Publishing
JavaScript, perl CGI, VBScript, Web Hosting
Apache, File Types, Internet Explorer,
Network, Printing, Processes
Outside the Cube
Auto, Computer Hardware,
Finances, Dating Scams,
Household, Male Dating Scammers,
Other Scams, Travel

Featured Article

Remove duplicate rows from a table - independent from indexes


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?


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:02:40 on Jul 20, 2019