DeniZone: Deleting duplicate records in SQL

[Blog bot]

Источник: http://denizone.blogspot.com/2009/11...ds-in-sql.html
==============

Recently I needed to delete some duplicate records across several companies in AX. The table is self-contained so I needn't worry about delete actions or validation and as there are several companies I wanted to try and do it SQL-style. So this is what I came up with

DELETE FROM [Table with Duplicates]
WHERE [Primary Key Field] IN
DELETE FROM [Table with Duplicates]
WHERE [Primary Key Field] IN
(
SELECT a.[Primary Key Field]
FROM [Table with Duplicates] a,
[Table with Duplicates] b

WHERE a.[Primary Key Field]!= b.[Primary Key Field] -- i.e. Userkey
AND a.[Value to check]= b.[Value to Check] -- i.e. Lastname
AND a.[Second Value to Check] = b.[Second Value to Check] -- i.e. Firstname
AND a.[Primary Key Field] < b.[Primary Key Field] -- i.e. Userkey
)

Respect to the original poster at SQL Server forum.

Remember always to backup your data and try the SQL-statement on a test table before you do anything on data which will be used in live environments.


Источник: http://denizone.blogspot.com/2009/11...ds-in-sql.html