Deleting Duplicates

ioates · August 29th, 2003, 05:54 AM

Hi - I have uploaded into a temp table approx. 300K of records, within this table there are 2 duplicate records. I want to delete these before I insert them into a base table and put a PKey on Col1

Col1
X0001
Y0000
X0001
Y0000

It does not matter which one of the duplicate records I delete. How can I do this with SQL??

TIA
Ian

pgtips · August 29th, 2003, 06:14 AM

Since you know that you only have 1 duplicate of each value, you can just use the ROWCOUNT option to prevent it deleting more than one row, like this:

Code:

SET ROWCOUNT 1
DELETE FROM <<tablename>>
WHERE Col1='X0001'
DELETE FROM <<tablename>>
WHERE Col1='Y0000'

ioates · August 29th, 2003, 06:25 AM

Thanks for the reply unfortunately, the duplicate values could change next time I update my base table, So a dummy scenario could be no duplicate values next time or 1 or more duplicates.

pgtips · August 29th, 2003, 06:37 AM

OIC you just want a general solution to this problem. The usual method is to
1. select distinct records into a temp table,
2. clear the original table
3. copy records back from temp table to original table

I'm sure you'll find plenty of examples if you search google with something like "SQL Server delete duplicate"

rgds
Phil

ioates · August 29th, 2003, 06:44 AM

Thanks Phil
This makes sense

Regards
Ian

Jeff Mason · August 31st, 2003, 10:18 AM

If your temporary table has a primary key column, then there is an alternative to copying the distinct rows into a temp table, deleting the source table then copying things back.

Let's assume you assigned an identity column as the primary key ('keycol' in my code below), and let's further assume you'd be happy to retain the first (lowest primary key value) row for any duplicates:

Code:

DELETE FROM YourTable
    WHERE keycol NOT IN
         (SELECT MIN(keycol)
            FROM YourTable
            GROUP BY Col1);

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com