DelphiFAQ Home Search:

Remove duplicate rows from a table - independent from indexes


comments10 comments. Current rating: 3 stars (6 votes). Leave comments and/ or rate it.


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. */

Content-type: text/html


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

2012-06-29, 20:02:37 from Japan  
<a href='' title='chanel bags'>chanel bags</a>
<a href='' title='chanel bags replica'>chanel bags replica</a>
<a href='' title='chanel handbags'>chanel handbags</a>
<a href='' title='Replica Chanel Bags'>Replica Chanel Bags</a>
<a href='' title='Cheap Chanel Handbags'>Cheap Chanel Handbags</a>
<a href='' title='Chanel Bags Sale'>Chanel Bags Sale</a>
<a href='' title='Chanel Handbags Store'>Chanel Handbags Store</a>
<a href='' title='discount chanel bags'>discount chanel bags</a>
<a href='' title='chanel bags outlet'>chanel bags outlet</a>
<a href='' title='cheap chanel bag'>cheap chanel bag</a>
<a href='' title='fake handbags chanel'>fake handbags chanel</a>
<a href='' title='chanel bags online'>chanel bags online</a>
<a href='' title='chanel bags free shipping'>chanel bags free shipping</a>
<a href='' title='chanel handbags free shipping'>chanel handbags free shipping</a>
<a href='' title='Replica Chanel Bags Online'>Replica Chanel Bags Online</a>
<a href='' title='Chanel Bags 2011 for Cheap'>Chanel Bags 2011 for Cheap</a>
<a href='' title='chanel handbags 2011 for sale'>chanel handbags 2011 for sale</a>
<a href='' title='Chanel khaki bags 2011'>Chanel khaki bags 2011</a>
<a href='' title='Chanel Bags 2011'>Chanel Bags 2011</a>
<a href='' title='chanel handbags 2011 for sale'>chanel handbags 2011 for sale</a>
<a href='' title='Chanel handbags 2012'>Chanel handbags 2012</a>
<a href='' title='Chanel Bags 2012'>Chanel Bags 2012</a>
<a href='' title='Replica Chanel Bag'>Replica Chanel Bag</a>
<a href='' title='2012 Chanel handbags'>2012 Chanel handbags</a>
<a href='' title='Chanel mini classic 2012'>Chanel mini classic 2012</a>
<a href='' title='Chanel coco 2012'>Chanel coco 2012</a>
<a href='' title='Chanel 2.55 Classic Flap Bag'>Chanel 2.55 Classic Flap Bag</a>
<a href='' title='Chanel 2.55 Classic Handbag Cheap'>Chanel 2.55 Classic Handbag Cheap</a>
<a href='' title='Chanel 2.55 Reissue Bags'>Chanel 2.55 Reissue Bags</a>
<a href='' title='Chanel 2.55 Reissue For Sale'>Chanel 2.55 Reissue For Sale</a>
<a href='' title='Chanel 2. 55 bag'>Chanel 2. 55 bag</a>
<a href='' title='Chanel Cambon Handbags'>Chanel Cambon Handbags</a>
<a href='' title='Chanel Cambon Bags Replica'>Chanel Cambon Bags Replica</a>
<a href='' title='replica Chanel cambon Bags'>replica Chanel cambon Bags</a>
<a href='' title='Chanel Clutch Bags'>Chanel Clutch Bags</a>
<a href='' title='Replica Chanel Clutch Handbags'>Replica Chanel Clutch Handbags</a>
<a href='' title='Chanel Clutch Handbags Online'>Chanel Clutch Handbags Online</a>
<a href='' title='Chanel Coco Bags'>Chanel Coco Bags</a>
<a href='' title='Chanel Coco Handbags'>Chanel Coco Handbags</a>
<a href='' title='Replica Chanel Bag'>Replica Chanel Bag</a>
<a href='' title='Replica Chanel Coco Handbags'>Replica Chanel Coco Handbags</a>
<a href='' title='Chanel bags shop'>Chanel bags shop</a>
<a href='' title='Chanel Mini Bags For Sale'>Chanel Mini Bags For Sale</a>
<a href='' title='Chanel Mini Handbags'>Chanel Mini Handbags</a>
<a href='' title='Chanel Mini Sale'>Chanel Mini Sale</a>
<a href='' title='Chanel mini bags replica'>Chanel mini bags replica</a>
<a href='' title='Chanel mini flap bags'>Chanel mini flap bags</a>
<a href='' title='cheap Chanel mini handbags'>cheap Chanel mini handbags</a>
<a href='' title='Cheap Chanel Purses'>Cheap Chanel Purses</a>
<a href='' title='Coco Chanel Purses'>Coco Chanel Purses</a>
<a href='' title='Chanel Purse Replica'>Chanel Purse Replica</a>
<a href='' title='Chanel leather purses'>Chanel leather purses</a>
<a href='' title='Coco Chanel purses'>Coco Chanel purses</a>
<a href='' title='Chanel wallet outlet'>Chanel wallet outlet</a>
<a href='' title='Chanel Tote Handbags'>Chanel Tote Handbags</a>
<a href='' title='Chanel Tote Bags'>Chanel Tote Bags</a>
<a href='' title='Replica Chanel Totes'>Replica Chanel Totes</a>
<a href='' title='Chanel Tote Handbags Sale 2012'>Chanel Tote Handbags Sale 2012</a>
<a href='' title='Chanel tote bag uk'>Chanel tote bag uk</a>
<a href='' title='discount Chanel Tote Bags'>discount Chanel Tote Bags</a>
<a href='' title='J12 Chanel Watches Sale'>J12 Chanel Watches Sale</a>
<a href='' title='Chanel J12 Ceramic Watch Online'>Chanel J12 Ceramic Watch Online</a>
<a href='' title='Replica Chanel J12 watches'>Replica Chanel J12 watches</a>
<a href='' title='Chanel J12 Ceramic Watches'>Chanel J12 Ceramic Watches</a>
<a href='' title='fake chanel watch'>fake chanel watch</a>
<a href='' title='Replica Chanel Sunglasses'>Replica Chanel Sunglasses</a>
<a href='' title='Cheap Chanel Sunglasses Outlet'>Cheap Chanel Sunglasses Outlet</a>
<a href='' title='replica Chanel sunglasses store'>replica Chanel sunglasses store</a>
<a href='' title='cheap Chanel sunglasses'>cheap Chanel sunglasses</a>
2012-09-19, 22:10:09
nvrdosay <a href=' '>order viagra online</a> =-] <a href=' '>cialis order on line</a> 4756
2013-02-04, 01:57:32
bypszbo <a href=' '>order discount viagra</a> 4608 <a href=' '>order cialis</a> IovXoU
2013-02-07, 03:37:33
bworvvme <a href=' '>viagra online</a> :-O <a href=' '>cialis online</a> BWlVvw
2013-03-03, 01:56:41
oiyelx <a href=' '>generic viagra</a> DsqXai <a href=' '>generic cialis</a> >:]]
2014-03-18, 00:39:39
2014-11-15, 08:30:24
anonymous from United Kingdom  
2014-12-26, 04:29:48
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.
2017-08-23, 01:51:45
anonymous from Indonesia  
kami sekeluarga tak lupa mengucapkan puji syukur kepada ALLAH S,W,T
dan terima kasih banyak kepada NYAI atas nomor togel.nya yang NYAI
berikan 4 angka 9050 alhamdulillah ternyata itu benar2 tembus NYAI.
dan alhamdulillah sekarang saya bisa melunasi semua utan2 saya yang
ada sama tetangga.dan juga BANK BRI dan bukan hanya itu NYAII. insya
allah saya akan coba untuk membuka usaha sendiri demi mencukupi
kebutuhan keluarga saya sehari-hari itu semua berkat bantuan NYAII..
sekali lagi makasih banyak ya NYAI… bagi saudara yang suka main togel
yang ingin merubah nasib seperti saya silahkan hubungi NYAI RONGGENG,,di no (((082-189-481-547)))
insya allah anda bisa seperti saya…menang togel 275
juta, wassalam.

dijamin 100% jebol saya sudah buktikan…sendiri….

Apakah anda termasuk dalam kategori di bawah ini !!!!

1?Dikejar-kejar hutang

2?Selaluh kalah dalam bermain togel

3?Barang berharga anda udah habis terjual Buat judi togel

4?Anda udah kemana-mana tapi tidak menghasilkan solusi yg tepat

5?Udah banyak Dukun togel yang kamu tempati minta angka jitunya
tapi tidak ada satupun yang berhasil..

Solusi yang tepat jangan anda putus asah….NYAI RONGGENG akan membantu
anda semua dengan Angka ritwal/GHOIB:
butuh angka togel 2D 3D 4D SGP / HKG / MALAYSIA / TOTO MAGNUM / dijamin
100% jebol
Apabila ada waktu
silahkan Hub: NYAI RONGGENG DI NO: (((082-189-481-547)))

angka GHOIB: singapur 2D/3D/4D/

angka GHOIB: hongkong 2D/3D/4D/

angka GHOIB; malaysia

angka GHOIB; toto magnum 4D/5D/6D/

angka GHOIB; laos 4D/6D/8D/

Angka GHOIB; thailan 2D/3D/

Angka GHOIB; macau 2D/3D/4D/

Angka GHOIB; sidney 2D/3D/4D/




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: