removing duplicates

removing duplicates

Postby alchemy9 » Wed 01 Feb 2006 10:24

Assuming I has duplicates in a column COl1in a table called dupe

Col1
-----
123
123
123
456

once I have identified the duplicates using the following quiery

select
distinct col1
from dupe
group by Col1
having count(Col1) >1

is there a way I could delete duplicates without putting a unique column on the table?

i was thinking of using

MSQuery1.delete; and going through the records of identified duplicates, or is there a way of using recNo


thanks in advance

Ian
alchemy9
 
Posts: 5
Joined: Wed 01 Feb 2006 06:39

Postby Ikar » Mon 06 Feb 2006 10:23

Try using SELECT statement with DISTINCT keyword. But it isn't the best way. May be better for you is to add to the table one more field with AUTO_INCREMENT attribute and to make it a key field. Please read MySQL Server manual for more information.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56


Return to MySQL Data Access Components