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

[Vadik]

Я надеюсь что это некий псевдо-SQL-код (не T-SQL), особенно учитывая что

Цитата:

and as there are several companies

[mazzy]

а также, то что он не смотрит ни в delete-actions, ни в ссылки на удаляемые записи

Цитата:

Сообщение от Blog bot

The table is self-contained so I needn't worry about delete actions or validation

[ZVV]

Человек обратился на "SQL-форум", там ему подсказали ответ на извечный вопрос "как найти дубликаты" со знаком ">", человеку понравилось, решил поделиться... ))

как-то уже обсуждалось тут:
Distinct в QueryBuildDataSource