Remove/Delete duplicate values from table in SQL Server

Total Post:61

Points:429
 1541  View(s)
Ratings:
Rate this:
Hi Expert!

I want to delete all duplicate records in a table. My table as following 

CREATE TABLE tblVisitor
(
S_No INT IDENTITY PRIMARY KEY,
Visitor_ID INT,
Visitor_Name VARCHAR(50),
Visited_Date DATETIME 
)

Table with data

S_No Visitor_ID Visitor_Name Visited Date

1 101 er. samuel fernandes 2013-02-16 16:34:24.307
2 102 hugh jackman 2013-03-06 16:34:24.307
3 101 er. samuel fernandes 2013-03-11 16:34:45.157
4 102 hugh jackman 2013-04-01 16:34:45.157
5 101 er. samuel fernandes 2013-04-10 16:36:57.423
6 102 hugh jackman 2013-04-18 16:36:57.423
7 103 brad pitt 2013-04-28 16:36:57.423
8 104 lois waisbrooker 2013-05-02 16:36:57.423
9 105 ezra heywood 2013-05-13 16:36:57.423
10 103 brad pitt 2013-05-16 16:36:57.423

Now I want to delete duplicate 'Visitor_ID' from tblVisitor 

Please help.
Thanks in advance! 
  1. Post:604

    Points:4228
    Re: Remove/Delete duplicate values from table in SQL Server

    Hi Brad!


    Try as following

    See Duplicate record

    SELECT * FROM tblVisitor WHERE S_No NOT IN (SELECT MIN(S_No) FROM tblVisitor GROUP BY Visitor_ID) 

    Delete duplicate record

    Delete tblVisitor WHERE S_No NOT IN (SELECT MIN(S_No) FROM tblVisitor GROUP BY Visitor_ID) 

      Modified On Mar-30-2018 12:56:09 AM

Answer