Home > DeveloperSection > Forums > Remove/Delete duplicate values from table in SQL Server
Brad Pitt
Brad Pitt

Total Post:61

Points:429
Posted on    May-16-2013 4:49 AM

 MSSQL Server MSSQL Server 
Ratings:


 1 Reply(s)
 910  View(s)
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! 


AVADHESH PATEL

Total Post:604

Points:4228
Posted on    May-16-2013 8:27 AM

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) 


Don't want to miss updates? Please click the below button!

Follow MindStick