SQL Server - How to delete duplicate rows from an SQL Server Table?

Hi Everyone,

Here is the example, which illustrates how to delete the duplicate records in SQL Server?

--Create a new table EMP

CREATE TABLE EMP (EMPID INT, NAME VARCHAR(100), SALARY INT)

GO

--Insert records to the table

INSERT INTO EMP (EMPID, NAME,SALARY)

SELECT 1, 'JOHN',2000

UNION ALL

SELECT 1, 'JOHN',2000

UNION ALL

SELECT 1, 'JOHN',2000

UNION ALL

SELECT 2, 'JOHN',2000

UNION ALL

SELECT 3, 'JOHN',2000

UNION ALL

SELECT 4, 'DAVID',3000

UNION ALL

SELECT 5, 'CHARLIE',4000

UNION ALL

SELECT 6, 'HARRY',2000

GO

In this example, I want to eliminate repeated values in the NAME and SALARY columns.

--Selecting the Data

SELECT * FROM EMP

GO

--Here is the solution

WITH EMP_ROW_NUM AS

(

  SELECT

    ROW_NUM = ROW_NUMBER() OVER( PARTITION BY NAME, SALARY

    ORDER BY NAME, SALARY ), *

  FROM EMP

)

DELETE FROM EMP_ROW_NUM WHERE ROW_NUM>1

You must specify the column names that you expect duplicate values in the PARTITION BY clause. In this example, NAME and SALARY.

--Dropping the table

DROP TABLE EMP

GO

Please share this, if this helps you. Thanks for reading my Blog !

Read More

Oracle - Use RANK to delete duplicate rows

Hi Everyone,

This is an example of the RANK function to identify and remove duplicate rows from Oracle tables, which deletes all duplicate rows while leaving the initial instance of the duplicate row.

DELETE FROM emp where ROWID IN

(SELECT ROWID FROM

(SELECT ROWID, rank_n FROM

(

SELECT 

RANK() OVER (PARTITION BY emp_id ORDER BY ROWID) rank_n, 

ROWID AS “ROWID" 

FROM 

emp 

WHERE emp_id IN (SELECT emp_id FROM emp GROUP BY emp_id, emp_name, desig HAVING COUNT(*) > 1)

)

)WHERE rank_n > 1

)

Method step-by-step

1. Finds the primary key (emp_id) of duplicate records

2. Rank the duplicate records (rank_n)

3. And then deletes all records other than with the rank 1.

Thanks for reading my blog !

Read More

Oracle: Delete duplicate rows - Using sub-query

Hi Everyone, 

Here we see an example of using SQL to delete duplicate table rows using an SQL sub query to identify duplicate rows, manually specifying the join columns:

DELETE 

FROM 

EMPLOYEE A

WHERE A.ROWID > ANY 

(

  SELECT 

    B.ROWID 

  FROM 

    EMPLOYEE B 

  WHERE A.EMPNO = B.EMPNO 

    AND A.ENAME = B.ENAME 

    AND A.JOB = B.JOB

)

Thanks for reading my Blog !

Read More