Delete exact dups from a table with dups based on all columns except one

Question

I want to delete the exact duplicate records from a table keeping one. But, i can't use the intermediate table approach because the dups are on all columns except the ID column. For ex:

ID,
COL1,
Col2,
col3,
col4
The dups are on col1, col2, col3, col4

Below some samples:

ID  COL1 COL2  COL3 COL4
123 ABC  4RTFD  FGY  12346
234 ABC  4RTFD  FGY  12346
586 ABC  4RTFD  FGY  12346

here only the Id column is different, rest four columns are duplicates. I want to keep the max ID column row only.

What approach can i use here?

Thanks, Amit

Answer 1

Try joining the table to itself on all columns and where ID is different...

    CREATE TABLE Dups
(
    ID int IDENTITY(1,1) PRIMARY KEY,
    Col1 int NOT NULL,
    Col2 date NOT NULL,
    Col3 char(1) NOT NULL,
    Col4 char(1) NOT NULL
 )
 INSERT dbo.Dups (Col1,Col2,Col3,Col4)
 VALUES ('1','20170925','A','Z'), ('1','20170925','A','Z'), ('1','20170925','A','Z'), ('2','20170925','A','Z'), ('2','20170925','A','Z'), ('2','20170925','A','Z'), ('3','20170925','A','Z');

 SELECT * FROM Dups;

 -- This solution to retain the first ID found that is duplicated...
 DELETE FROM Dups
 WHERE ID IN (
                SELECT ID
                FROM (
                        SELECT d1.ID,
                                row_number() OVER (ORDER BY d1.ID) AS DupSeq
                        FROM dbo.Dups AS d1
                        INNER JOIN dbo.Dups AS d2 ON d2.Col1 = d1.Col1 AND d2.Col2 = d1.Col2 AND d2.Col3 = d1.Col3 AND d2.Col4 = d1.Col4
                        WHERE d1.ID <> d2.ID
                    ) AS t
                WHERE DupSeq > 1
            );

 -- This solution to retain the last ID found that is duplicated...
 DELETE FROM Dups
 WHERE ID NOT IN (
                SELECT DISTINCT
                       max(t.ID) OVER(PARTITION BY t.Col1,t.Col2,t.Col3,t.Col4 ORDER BY WindowOrder) AS KeepID
                FROM (
                        SELECT d1.ID,
                                d1.Col1,
                                d1.Col2,
                                d1.Col3,
                                d1.Col4,
                                1 AS WindowOrder
                        FROM dbo.Dups AS d1
                        LEFT OUTER JOIN dbo.Dups AS d2 ON  d2.Col1 = d1.Col1 
                                                       AND d2.Col2 = d1.Col2 
                                                       AND d2.Col3 = d1.Col3 
                                                       AND d2.Col4 = d1.Col4
                                                       AND d1.ID <> d2.ID
                    ) AS t
            );


 SELECT * FROM Dups;

DROP TABLE dbo.Dups

You'll need the row_number() in the first solution as clearly ID1 will match ID3 and so ID3 will then match ID1 as well.

In the second solution, the join is LEFT OUTER to retain those values that are not duplicated.

Answer 2

You can do, what many others have done before in SQL-Server (and Teradata), see here How to delete duplicate rows in sql server? or you can do it even without a CTE like

DELETE FROM (
  SELECT ROW_NUMBER()
  OVER (PARTITION BY col1,col1,col3,col4
        ORDER BY ID DESC) rn
  FROM tbl  -- tbl is "your" table ...
) t1 WHERE rn>1

It works on SQL, haven't tested it on teradata, but, since ROW_NUMBER() exists there too I, expect it to work ...

Answer 3

You can use correlated subquery and max function to achieve your desired result as below.

DELETE
FROM table1 t1
WHERE t1.Id <> (
        SELECT max(t2.Id)
        FROM table1 t2
        WHERE t1.col1 = t2.col1
            AND t1.col2 = t2.col2
            AND t1.col3 = t2.col3
            AND t1.col4 = t2.col4
        );

The above query assumes table1 as your table name.

select * from table1;

Result:

ID  Col1    Col2    Col3    Col4
---------------------------------
586 ABC    4RTFD    FGY    12,346

You can check demo *here

Update:

Below rows are added to the sample data set.

id  col1    col2    col3    col4
----------------------------------
345 XYZ    4FTFD    FGY     12346
745 XYZ    4FTFD    FGY     12346
945 XYZ    4FTFD    FGY     12346

Result:

id   col1    col2   col3    col4
-----------------------------------
586  ABC    4RTFD   FGY     12346
945  XYZ    4FTFD   FGY     12346

DEMO

*Note: Due to un-availability of teradata online demo tool, PostgreSQL demo has been used as correlated subquery is supported by PostgreSQL. Query has also been simulated on local teradata environment.

Answer 4

Isn't that a simple usage of the grouping functionality?

select max(ID) ID, COL1, COL2, COL3
from tableA
group by 2,3,4

and save it into a new table. If it's necessary to delete the duplicate rows from the existing table you can perform following delete statement:

delete from tableA as a1
    where (
        select 1 from (
            select max(ID) ID, COL1, COL2, COL3 from tableA group by 2,3,4) a2
        where a1.ID = a2.ID
            and a1.COL1 = a2.COL1
            and a1.COL2 = a2.COL2
            and a1.COL3 = a2.COL3
         ) is null