How to delete duplicate records from SQL Server?

Question

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.

CREATE_DATE is the column that captures when I loaded the file.
file_id is from the id I am generating based on the file_log_sys table
stage_id is an identity column

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.

Possible duplicate of [How to delete duplicate rows in sql server?](https://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server) — Jose Bagatelli, Apr 05 '19 at 10:48

score 3 · Accepted Answer · answered Apr 05 '19 at 10:30

3

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

answered Apr 05 '19 at 10:30

Fahmi

37,315
5
22
31

1

Thank you it worked. This is what i was expecting to see my results – swathi Apr 05 '19 at 10:43

score 0 · Answer 2 · answered Apr 05 '19 at 10:32

0

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';

answered Apr 05 '19 at 10:32

Jose Bagatelli

1,367
1
15
32

1

Sorry i might confused you, no i don't want to hard code the values . I gave an example that i want to delete the that particular records from the above set i mentioned. I will have many duplicated records in the table so i am trying to see if i use MIN funcation and delete the duplicate records – swathi Apr 05 '19 at 10:37
In this case you should accept the answer from @fa06. It will do exactly what you're looking for. Although your question is a duplication of https://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server – Jose Bagatelli Apr 05 '19 at 10:47

score 0 · Answer 3 · answered Apr 05 '19 at 12:29

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)

How to delete duplicate records from SQL Server?

3 Answers3