REMOVE/DELETE DUPLICATE VALUES FROM TABLE IN SQL SERVER

Brad Pitt

Total Post:61

Points:429
Posted by  Brad Pitt
 1224  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. AVADHESH PATEL

    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) 

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!