I need to remove all duplicate records.

[mdub]

Hi,

I need to remove all duplicate records in a table. These are records that have matching Contract and Order fields. I can't do a DISTINCT query, because that still leaves one record. Thanks.

Michael

 

[ByteMyzer]

But, if you use a distinct query, and it shows one record, then you don't have a duplicate. What's the problem?

Your request is not entirely clear. Could you describe, more explicitly, what you are attempting to accomplish, possibly with some before-and-after sample data?

 

[mdub]

deleting duplicates

Thanks for your reply. I need to delete all duplicate records. For instance, if records 1,2, and 3 have identical Orders and Contracts, all 3 records need to be deleted, not just records 2 and 3.

Michael

 

[ed_the_unlucky]

i would think that if you did a simple select query based upon the field(s) that define dupes and ran a delete query on the resulting records, that would do the trick, no?

i guess the real question is what defines uniqueness?

ed

 

[ByteMyzer]

Try the following SQL (substitute the highlighted text with the actual table/field names):

DELETE T1.*
FROM [b][i]MyTable[/i][/b] T1
WHERE EXISTS
 (SELECT T2.[b][i]OrderID[/i][/b], T2.[b][i]ContractID[/i][/b]
  FROM [b][i]MyTable[/i][/b] T2
  GROUP BY T2.[b][i]OrderID[/i][/b], T2.[b][i]ContractID[/i][/b]
  HAVING T2.[b][i]OrderID[/i][/b] = T1.[b][i]OrderID[/i][/b]
  AND T2.[b][i]ContractID[/i][/b] = T1.[b][i]ContractID[/i][/b]
  AND Count(T2.[b][i]OrderID[/i][/b])>1
 )
;

 

[mdub]

Thanks....

I tried your query and it works. I solved the problem a different way by first getting a table of all the contracts and orders that are duplicates, then putting them in a delete query, that takes out all the duplicate rows. At least it works.