Removing duplicate records from query results

[goslincm]

Hi, I ran a find duplicates query based on my field name [CTV_ID]. So now that I can see how many duplicate records I have, is there anyway to delete all but one?

 

[deanbri75]

I don't know if this will help, my situation sounds similar and here's how I dealt with it. I'm sure there's a more elegant way but this worked well for me.

I have to import data into TableA - this is the one with the duplicates. I want to copy rows into TableB but only if the row doesn't already exist in TableB. In my case, I use the primary key to help with this.

INSERT INTO TableB
SELECT     *
FROM         TableA
WHERE     (PrimaryKey NOT IN
                          (SELECT     TableB.PrimaryKey
                            FROM          TableB))

This sort of makes TableA a prep table. Again, this might not apply to your situation but it works for me.

 

[PHV]

Why not have a look in the FAQ area before posting ?
faq701-5721

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886