Delete duplicate records
Delete Duplicate Records using SQL
By duplicate record I mean that every field in one record is
identical to every field in a different record, i.e. a duplicate is
where there is no way of telling two or more records apart. If you
just need to remove records which are similar (i.e. one or more
fields are identical but there are one or more fields which are
different) then instead refer to how to delete
To check that you have duplicate records in your table do the
select count(*) from MyTable
select distinct * from MyTable
unfortunately SQL does not permit a select count(distinct). You
have duplicate records if the number of records returned by the
second query is less than the number of records returned by the
Unfortunately there is no way in SQL to delete one of these
duplicates without deleting all of them. They are identical after
all, so there is no sql query that you could put together which
could distinguish between them.
What you can do is to copy all the distinct records into a new
select distinct * into NewTablefrom MyTable
This query will create a new table (NewTable in my example)
containing all the records in the original table but without any
records being duplicated. It will therefore preserve a single copy
of those records which were duplicated.
Because this query creates (and populates) a new table, it will
fail if the table already exists.
To delete similar records, i.e. where the records are not the
same but one field is the same and only one copy needs to be
preserved, try the following SQL:
delete from T1 from MyTable T1, MyTable T2 where T1.dupField = T2.dupField and T1.uniqueField > T2.uniqueField
This will delete all records from the table MyTable which have
the same value for the field dupField, leaving that record which has
the lowest value in uniqueField.