CODEWITHSUNDEEP
sqlsql-servert-sql
swathi
swathi

Reputation: 91

How to delete duplicate records from SQL Server?

I have a table with records and I want to delete all duplicate records name, age, date are columns I am getting from a file.

Sample data:

stage_id name age       date         file_id  create_date
---------------------------------------------------------
   1     john   25     2019-02-02    100       2019-04-04
   2     sam    50     2019-01-13    100       2019-04-04
   3     john   25     2019-02-02    101       2019-04-05   
   4     peter  33     2019-01-01    101       2019-04-05

I would like to delete the record where file_id = 100 and stage_id = 1 and name= john

I tried:

delete min(file) id 
from table

and it didn't work.

I want my output to be 

stage_id name   age    date        file_id     create_date
-----------------------------------------------------------
   2     sam    50     2019-01-13    100       2019-04-04
   3     john   25     2019-02-02    101       2019-04-05   
   4     peter  33     2019-01-01    101       2019-04-05

Can you please help me with the query?

Thanks in advance.

Upvotes: 2

Views: 81

Answers (3)

THE LIFE-TIME LEARNER
THE LIFE-TIME LEARNER

Reputation: 1522

Using RowNumber()

WITH TablesCTE AS
(
   SELECT *, ROW_NUMBER()OVER(PARTITION BY file_id ORDER BY file_id  ) AS RowNumber
   FROM TableName
)
DELETE FROM TablesCTE WHERE RowNumber > 1

Alternative Way

DELETE FROM TableName WHERE file_id  NOT IN (SELECT MIN(file_id ) _
    FROM TableName  GROUP BY stage_id,name,age,date)

Upvotes: 0

Jose Bagatelli
Jose Bagatelli

Reputation: 1409

Not sure if I fully understand your question, but if you just want to delete the record where the file_id = 100 and stage_id = 1 and name= john it is as simple as:

delete from your_table where file_id = 100 and stage_id = 1 and name = 'john';

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

use row_number()

with cte as
(
    select *,row_number() over(partition by name order by create_Date desc) as rn
    from tablename
)

delete from cte where rn<>1

Upvotes: 3

Related Questions