How Do You Delete Duplicate Records In SQL

In SQL Server 2005 and above:

WITH    q AS
        (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY dup_column ORDER BY dup_column) AS rn
        FROM    mytable
        )
DELETE
FROM    q
WHERE   rn > 1

Here is how to do it in Oracle, using ROWID. Different flavours of RDBMS will have their own equivalent.

I start by creating some duplicate records ...

SQL> select t, count(*) from t23 group by t;

T       COUNT(*)
----- ----------
09:00          2
12:00          2
10:30          2
11:00          2
12:30          2
08:00          2
10:45          2
11:15          2

8 rows selected.

SQL>

... and now I zap them, using T to define "duplicate records"...

SQL> delete from t23
  2  where rowid > ( select min(rowid) from t23 x
  3                  where x.t = t23.t )
  4  /

8 rows deleted.

SQL> select t, count(*) from t23 group by t;

T       COUNT(*)
----- ----------
09:00          1
12:00          1
10:30          1
11:00          1
12:30          1
08:00          1
10:45          1
11:15          1

8 rows selected.

SQL>

Note that in the sub-query you have to include as many columns as necessary to specify what constitutes uniquenss. This could end up being the whole record, although one would hope not.

Incidentally, the most efficient way of doing this is not to have duplicate records in the first place. Which is why Nature gave us primary keys and unique constraints.

Since you don't have a key on the table (assuming your rows are 100% duplicated), you won't have any problems with other tables referencing your table with a FOREIGN KEY.

The fastest and least complicated way of doing this is:

SELECT DISTINCT *
INTO #tmp
FROM YourTable;

TRUNCATE TABLE YourTable;

INSERT YourTable
SELECT * from #tmp;

Maybe consider adding some version of this statement to the end ;-)

ALTER YourTable ADD CONSTRAINT PK_YourTable PRIMARY KEY (whatever, keeps, this, from, happening, again);

In Postgresql, I use the following:

DELETE FROM table_name q
WHERE EXISTS (
   SELECT 1
   FROM   table_name q1
   WHERE  q1.ctid < q.ctid
   AND    q.fid = q1.fid
   );

Where the fid is your unique id number, or primary key.

In MS SQL,

DELETE Table1 FROM Table1 
INNER JOIN ( 
   SELECT MAX(lineitem) AS lineitem, ID, COUNT (ID) AS IDCount 
   FROM Table1 
   GROUP BY ID HAVING COUNT (ID) > 1) AS Table2 
ON Table1.ID = Table2.ID and Table1.LineItem = Table2.lineitem

Above SQL will find all duplicate ID and delete the one with maximum LineItem.

ID         LineItem
---        --------
111        1
111        2 (Deleted)
222        1
222        2 (Deleted)

In Oracle, We can do it using many ways.

1) By Creating a New Table :

create table emp2 as
select distinct * from EMP;
drop table emp;
alter table emp2 rename to emp; 

2) By Using RowID :

delete from EMP where rowid not in (
select max(rowid) from EMP group by EMPNO, EMPNAME, SALARY); 

3) By Using Self Join and RowID :

delete from emp e1 where rowid not in(
select max(rowid) from EMP e2
where e1.EMPNO = e2.EMPNO); 

select col from table;

select distinct col from table;