DelphiFAQ Home Search:

Remove duplicate rows from a table - independent from indexes

 

comments11 comments. Current rating: 3 stars (6 votes). Leave comments and/ or 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:
  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. */

Comments:

2012-06-06, 19:29:14
anonymous from United States  



Keywords:
2012-06-18, 21:46:14
anonymous from Colombia  
One alleged victim would routinely throw away his underwear to hide [url= http://www.louisvui..net/]Louis Vuitton Bags[/url] the supposed abuse. Week two of the trial began with a final prosecution witness. The mother of Victim No. 9 took the [url= http://www.chanelou..et/]Chanel Purses[/url] stand Monday to detail her son’s cover-up of the alleged sordid acts he suffered at Sandusky’s hands. “I [url= http://www.guccibel..rg/]Hermes Belts[/url] always wondered why he never had any underwear in the laundry,” she testified. “He just told me he had an [url= http://www.beltsout...org/]Mens Belts[/url] accident and he threw them out.” Indeed, she testified that he started becoming “sick” more often after [url= http://www.topbelts..org/]Gucci Belts[/url] spending time with the ex-Penn State coach. Victim No. 9 supposedly slept over at the Sandusky residence more than 100 times.Victim No. 9 testified [url= http://www.louisvui..net/]Louis Vuitton Handbags[/url] last Thursday that he screamed during one episode of alleged abuse, hoping that [url= http://www.beltsonl..com/]Louis Vuitton Belts[/url] Sandusky’s wife, who he said was always upstairs, would hear. “I think the basement is soundproof,” he said [url= http://www.chanels-..om/]Chanel Handbags[/url] tearfully, noting that Sandusky’s wife Dottie never did come down.Lead defense attorney Joe Amendola has also [url= http://www.chanelba..om/]Chanel Handbag[/url] suggested previously that the accusers and the Attorney General’s office might be colluding. Lead prosecutor Joe [url= http://www.lvshopos..net/]Louis Vuitton Purses[/url] McGettigan has often queried the alleged victims while on the stand: “Have I ever, ever, ever told you what to say?” which they’ve answered with resounding denials. But it could be a larger issue for Kline and the other subpoenaed attorneys to defend themselves against a [url= http://www.louisvui..com/]Louis Vuitton Purse[/url]
charge of violating the gag order issued by Judge Cleland, which bars any representative from either side from discussing the case with the media.


2012-06-29, 20:02:37
chanelissohot@gmail.com from Japan  
<a href=' http://www.chanel-b..lica.com/' title='chanel bags'>chanel bags</a>
<a href=' http://www.chanel-b..lica.com/' title='chanel bags replica'>chanel bags replica</a>
<a href=' http://www.chanel-b..lica.com/' title='chanel handbags'>chanel handbags</a>
<a href=' http://www.chanel-b..lica.com/' title='Replica Chanel Bags'>Replica Chanel Bags</a>
<a href=' http://www.chanel-b..lica.com/' title='Cheap Chanel Handbags'>Cheap Chanel Handbags</a>
<a href=' http://www.chanel-b..lica.com/' title='Chanel Bags Sale'>Chanel Bags Sale</a>
<a href=' http://www.chanel-b..lica.com/' title='Chanel Handbags Store'>Chanel Handbags Store</a>
<a href=' http://www.chanel-b..lica.com/' title='discount chanel bags'>discount chanel bags</a>
<a href=' http://www.chanel-b..lica.com/' title='chanel bags outlet'>chanel bags outlet</a>
<a href=' http://www.chanel-b..lica.com/' title='cheap chanel bag'>cheap chanel bag</a>
<a href=' http://www.chanel-b..lica.com/' title='fake handbags chanel'>fake handbags chanel</a>
<a href=' http://www.chanel-b..lica.com/' title='chanel bags online'>chanel bags online</a>
<a href=' http://www.chanel-b..lica.com/' title='chanel bags free shipping'>chanel bags free shipping</a>
<a href=' http://www.chanel-b..lica.com/' title='chanel handbags free shipping'>chanel handbags free shipping</a>
<a href=' http://www.chanel-b..lica.com/' title='Replica Chanel Bags Online'>Replica Chanel Bags Online</a>
<a href=' http://www.chanel-b..c-67.html' title='Chanel Bags 2011 for Cheap'>Chanel Bags 2011 for Cheap</a>
<a href=' http://www.chanel-b..c-67.html' title='chanel handbags 2011 for sale'>chanel handbags 2011 for sale</a>
<a href=' http://www.chanel-b..c-67.html' title='Chanel khaki bags 2011'>Chanel khaki bags 2011</a>
<a href=' http://www.chanel-b..c-66.html' title='Chanel Bags 2011'>Chanel Bags 2011</a>
<a href=' http://www.chanel-b..c-66.html' title='chanel handbags 2011 for sale'>chanel handbags 2011 for sale</a>
<a href=' http://www.chanel-b..c-56.html' title='Chanel handbags 2012'>Chanel handbags 2012</a>
<a href=' http://www.chanel-b..c-56.html' title='Chanel Bags 2012'>Chanel Bags 2012</a>
<a href=' http://www.chanel-b..c-56.html' title='Replica Chanel Bag'>Replica Chanel Bag</a>
<a href=' http://www.chanel-b..c-56.html' title='2012 Chanel handbags'>2012 Chanel handbags</a>
<a href=' http://www.chanel-b..c-56.html' title='Chanel mini classic 2012'>Chanel mini classic 2012</a>
<a href=' http://www.chanel-b..c-56.html' title='Chanel coco 2012'>Chanel coco 2012</a>
<a href=' http://www.chanel-b..c-59.html' title='Chanel 2.55 Classic Flap Bag'>Chanel 2.55 Classic Flap Bag</a>
<a href=' http://www.chanel-b..c-59.html' title='Chanel 2.55 Classic Handbag Cheap'>Chanel 2.55 Classic Handbag Cheap</a>
<a href=' http://www.chanel-b..c-58.html' title='Chanel 2.55 Reissue Bags'>Chanel 2.55 Reissue Bags</a>
<a href=' http://www.chanel-b..c-58.html' title='Chanel 2.55 Reissue For Sale'>Chanel 2.55 Reissue For Sale</a>
<a href=' http://www.chanel-b..c-58.html' title='Chanel 2. 55 bag'>Chanel 2. 55 bag</a>
<a href=' http://www.chanel-b..c-61.html' title='Chanel Cambon Handbags'>Chanel Cambon Handbags</a>
<a href=' http://www.chanel-b..c-61.html' title='Chanel Cambon Bags Replica'>Chanel Cambon Bags Replica</a>
<a href=' http://www.chanel-b..c-61.html' title='replica Chanel cambon Bags'>replica Chanel cambon Bags</a>
<a href=' http://www.chanel-b..c-65.html' title='Chanel Clutch Bags'>Chanel Clutch Bags</a>
<a href=' http://www.chanel-b..c-65.html' title='Replica Chanel Clutch Handbags'>Replica Chanel Clutch Handbags</a>
<a href=' http://www.chanel-b..c-65.html' title='Chanel Clutch Handbags Online'>Chanel Clutch Handbags Online</a>
<a href=' http://www.chanel-b..c-62.html' title='Chanel Coco Bags'>Chanel Coco Bags</a>
<a href=' http://www.chanel-b..c-62.html' title='Chanel Coco Handbags'>Chanel Coco Handbags</a>
<a href=' http://www.chanel-b..c-62.html' title='Replica Chanel Bag'>Replica Chanel Bag</a>
<a href=' http://www.chanel-b..c-62.html' title='Replica Chanel Coco Handbags'>Replica Chanel Coco Handbags</a>
<a href=' http://www.chanel-b..c-62.html' title='Chanel bags shop'>Chanel bags shop</a>
<a href=' http://www.chanel-b..c-64.html' title='Chanel Mini Bags For Sale'>Chanel Mini Bags For Sale</a>
<a href=' http://www.chanel-b..c-64.html' title='Chanel Mini Handbags'>Chanel Mini Handbags</a>
<a href=' http://www.chanel-b..c-64.html' title='Chanel Mini Sale'>Chanel Mini Sale</a>
<a href=' http://www.chanel-b..c-64.html' title='Chanel mini bags replica'>Chanel mini bags replica</a>
<a href=' http://www.chanel-b..c-64.html' title='Chanel mini flap bags'>Chanel mini flap bags</a>
<a href=' http://www.chanel-b..c-64.html' title='cheap Chanel mini handbags'>cheap Chanel mini handbags</a>
<a href=' http://www.chanel-b..c-63.html' title='Cheap Chanel Purses'>Cheap Chanel Purses</a>
<a href=' http://www.chanel-b..c-63.html' title='Coco Chanel Purses'>Coco Chanel Purses</a>
<a href=' http://www.chanel-b..c-63.html' title='Chanel Purse Replica'>Chanel Purse Replica</a>
<a href=' http://www.chanel-b..c-63.html' title='Chanel leather purses'>Chanel leather purses</a>
<a href=' http://www.chanel-b..c-63.html' title='Coco Chanel purses'>Coco Chanel purses</a>
<a href=' http://www.chanel-b..c-63.html' title='Chanel wallet outlet'>Chanel wallet outlet</a>
<a href=' http://www.chanel-b..c-60.html' title='Chanel Tote Handbags'>Chanel Tote Handbags</a>
<a href=' http://www.chanel-b..c-60.html' title='Chanel Tote Bags'>Chanel Tote Bags</a>
<a href=' http://www.chanel-b..c-60.html' title='Replica Chanel Totes'>Replica Chanel Totes</a>
<a href=' http://www.chanel-b..c-60.html' title='Chanel Tote Handbags Sale 2012'>Chanel Tote Handbags Sale 2012</a>
<a href=' http://www.chanel-b..c-60.html' title='Chanel tote bag uk'>Chanel tote bag uk</a>
<a href=' http://www.chanel-b..c-60.html' title='discount Chanel Tote Bags'>discount Chanel Tote Bags</a>
<a href=' http://www.chanel-b..c-74.html' title='J12 Chanel Watches Sale'>J12 Chanel Watches Sale</a>
<a href=' http://www.chanel-b..c-74.html' title='Chanel J12 Ceramic Watch Online'>Chanel J12 Ceramic Watch Online</a>
<a href=' http://www.chanel-b..c-74.html' title='Replica Chanel J12 watches'>Replica Chanel J12 watches</a>
<a href=' http://www.chanel-b..c-74.html' title='Chanel J12 Ceramic Watches'>Chanel J12 Ceramic Watches</a>
<a href=' http://www.chanel-b..c-74.html' title='fake chanel watch'>fake chanel watch</a>
<a href=' http://www.chanel-b..c-73.html' title='Replica Chanel Sunglasses'>Replica Chanel Sunglasses</a>
<a href=' http://www.chanel-b..c-73.html' title='Cheap Chanel Sunglasses Outlet'>Cheap Chanel Sunglasses Outlet</a>
<a href=' http://www.chanel-b..c-73.html' title='replica Chanel sunglasses store'>replica Chanel sunglasses store</a>
<a href=' http://www.chanel-b..c-73.html' title='cheap Chanel sunglasses'>cheap Chanel sunglasses</a>
2012-07-03, 02:06:26
katterlin650@gmail.com from Korea (R)  
Within hustle [url= http://michaelkorso..o/]michael kors outlet sales[/url] in addition to [url= http://www.michaelk..c-]michael kors iphone[/url] bustle connected with
[url= http://www.michaelk..ml]michael kors watches[/url] today's universe, individuals
[url= http://www.michaelk..ml]michael kors wallets[/url] normally tend to Michael Kors Outlet Sales neglect the worthiness [url= http://michaelkors2..m/]michael kors store[/url] of dwelling an active lifestyle. People [url= http://www.nike-sho...org/]nike air shox[/url] right now are so busy
[url= http://www.cheapcoa..eaa8]coach purses[/url] within stitching the correct
[url= http://www.us-edhar..10.html]ed hardy shoes[/url] balance in between high Cheap Air
Jordan 11 expenses and reduced [url= http://www.michaelk..ml]michael kors handbags[/url] incomes which finding
[url= http://www.michaelk..ml]michael kors bags[/url] sometime out for physical [url= http://www.nike-sho..-c-]womens nike shoes[/url] activities almost certainly
[url= http://www.airjorda..7.html]air jordan shoes[/url] lands throughout the very
[url= http://www.airjorda..html]Cheap Air Jordan 3 [/url] continue spot within everybody's [url= http://www.us-edhar..irts-c-]ed hardy shirts[/url] from a caterer. Moreover, the high-price tags
[url= http://www.cheapcoa..html]coach handbags[/url] linked with sports items in addition to
[url= http://coachpursess..com/]coach outlet[/url] equipments is actually but an added factor [url= http://www.michaelk..c-]michael kors watch[/url] that has further ignited a decrease in men and women today [url= http://michaelkors1..m/]michael kors online[/url] receiving drawn in direction of adopting any
[url= http://michaelkorso..m/]michael kors outlet[/url] sporty way of living. On online retailers in addition to [url= http://www.michaelk..ml]michael kors bag[/url] sports makes have right now launched
[url= http://www.michaelk..ml]michael kors purse[/url] thousands of promotional offers and [url= http://nike-shox-sh..com/]cheap nike shox[/url] money-saving offers on hottest sports gadgets to attract a significant quantity connected with customers.
2012-09-19, 22:10:09
 
rating
nvrdosay <a href=' http://orderviagrafa.com/ '>order viagra online</a> =-] <a href=' http://ordercialisfa.com/ '>cialis order on line</a> 4756
2013-02-04, 01:57:32
 
rating
bypszbo <a href=' http://orderviagratd.com/ '>order discount viagra</a> 4608 <a href=' http://ordercialistd.com/ '>order cialis</a> IovXoU
2013-02-07, 03:37:33
 
rating
bworvvme <a href=' http://viagraonlinete.com/ '>viagra online</a> :-O <a href=' http://cialisonlinete.com/ '>cialis online</a> BWlVvw
2013-03-03, 01:56:41
 
rating
oiyelx <a href=' http://genericviagrafsb.com/ '>generic viagra</a> DsqXai <a href=' http://genericcialisfsb.com/ '>generic cialis</a> >:]]
2014-03-18, 00:39:39
ujxihlfg  
rating
tchnesc
2014-11-15, 08:30:24
anonymous from United Kingdom  
rating
2014-12-26, 04:29:48
anonymous  
Ooh that's a great blog! I am such a big geek. Most of it has to do with me being on the internet, havnig so many places that I frequent on there, as well as how I play video games all day and read science fiction novels and watch the scifi channel.

 

 

NEW: Optional: Register   Login
Email address (not necessary):

Rate as
Hide my email when showing my comment.
Please notify me once a day about new comments on this topic.
Please provide a valid email address if you select this option, or post under a registered account.
 

Show city and country
Show country only
Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:

Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity.
The owner of this web site reserves the right to delete such material.

photo Add a picture: