CODEWITHSUNDEEP
sql
user6347596
user6347596

Reputation:

How to delete multiple rows from a table

Here I have a simple table with multiple duplicate rows:

Customer

Id   Name 
-----------
1    John
2    John
3    Mick
4    Mick
5    John

I wrote a simple query to find no of duplicate rows:

With EmployeeDuplicat As
(
    Select 
        *, ROW_NUMBER() over(PARTITION BY id ORDER BY id) As RowNum
    From 
        Customer
)
Select * 
From EmployeeDuplicat

This returns an output of:

Id    Name    RowNum
---------------------
1     John       1
2     John       1
3     Mick       1
4     Mick       1
5     John       1

Upvotes: 0

Views: 113

Answers (1)

wannadream
wannadream

Reputation: 1760

WITH EmployeeDuplicat AS
(
    SELECT 
        *, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY id) AS RowNum
    FROM
        Customer
)
DELETE FROM Customer 
WHERE Id IN (SELECT Id 
             FROM EmployeeDuplicat 
             WHERE RowNum > 1)

Upvotes: 1

Related Questions