I am trying to write a SQL statement to both find and delete duplicate
records in a DB Table. I've tested this a bit and it seems to work. This
table, "Payments" is related to an Orders table through a PK "OrderID" on
the Orders table and a FK "OrderID" on the payments table. The PK for the
Payments table is "PaymentID".

An Order record could ligimately have multiple payment records associated
with it, which is why I've included the extra search criteria in the SQL
since its improbable that there would be two ligitmate records where all the
criteria matched.

Can anyone see anything immediately incorrect?

Thank you,

Lee


SELECT P.OrderID, P.PaymentID, P.PaymentMethod,
P.PaymentType,
P.PaymentAmount,
P.PaymentDate FROM Payments P
WHERE
OrderID IN
(SELECT S.OrderID FROM Payments S
GROUP BY S.PaymentID, S.OrderID, S.PaymentType, S.PaymentMethod,
S.PaymentAmount, S.PaymentDate
HAVING Count(OrderID) > 0 AND
S.PaymentType = P.PaymentType AND
S.PaymentMethod = P.PaymentMethod AND
S.PaymentDate = P.PaymentDate AND
S.PaymentAmount = P.PaymentAmount AND
S.PaymentID <> P.PaymentID
)
ORDER BY
OrderID


DELETE FROM Payments P
WHERE
OrderID IN
(SELECT S.OrderID FROM Payments S
GROUP BY S.PaymentID, S.OrderID, S.PaymentType, S.PaymentMethod,
S.PaymentAmount, S.PaymentDate
HAVING Count(OrderID) > 0 AND
S.PaymentType = P.PaymentType AND
S.PaymentMethod = P.PaymentMethod AND
S.PaymentDate = P.PaymentDate AND
S.PaymentAmount = P.PaymentAmount AND
S.PaymentID <> P.PaymentID AND
S.OrderID = P.OrderID
)