problem with duplicates 1

[Leakyradiator]

Hi:

I have a database with duplicate records. Apparently, "overwrite existing date" was not selected when using DTS, so all the data exists twice (including primary keys).

I tried to delete the items using Enterprise manager, and it wouldn't let me do it. I don't know how to delete a duplicate record using SQL (with out deleting both records). Any advice is greatly appreciated.

 

[Genimuse]

One common method for removing duplicates is to create a second identical table and then

 SELECT DISTINCT *

into the new table, followed by deleting the original and renaming the new (or clearing out the original and copying back all of the records from the temp table).

 

[SiJP]

Genimuse,

I had a table that had ID numbers as follows:

10114
10114
10114
10114
10114
10652
10652
10652
10652
10652
22454
22454
22454
22454

etc...

I was playing around for ages with creating recordsets and using nomatch etc to no avail... this is a perfect answer so have a star!

Thanks