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? — Jose Bagatelli
– Jose Bagatelli, Commented Apr 5, 2019 at 10:48

Fahmi · Accepted Answer · 2019-04-05 10:30:48Z

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 5, 2019 at 10:30

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

swathi Over a year ago

Thank you it worked. This is what i was expecting to see my results

Jose Bagatelli · Accepted Answer · 2019-04-05 10:32:42Z

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 5, 2019 at 10:32

Jose Bagatelli

1,4111 gold badge20 silver badges35 bronze badges

2 Comments

swathi Over a year ago

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

Jose Bagatelli Over a year ago

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 stackoverflow.com/questions/18390574/…

Akhileshkumar singh · Accepted Answer · 2019-04-05 12:29:15Z

0

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)

answered Apr 5, 2019 at 12:29

Akhileshkumar singh

1,5322 gold badges10 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to delete duplicate records from SQL Server?

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related