.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> SQL Query >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

How to Delete a Repeting(or)duplicate records from a Table using SqlServer?

Posted By:Syed Shakeer Hussain       Posted Date: March 14, 2010    Points: 10    Category: SQL Query    URL: http://www.dotnetspark.com  

Delete a Repeting(or)duplicate records from a Table?
 

create table dupemp
(
name varchar(20),salary int,deptno int
)

insert into dupemp(name,salary,deptno) values('inthiyaaz',2000,10)

insert into dupemp(name,salary,deptno) values('inthiyaaz',2000,10)

insert into dupemp(name,salary,deptno) values('inthiyaaz',2000,10)

insert into dupemp(name,salary,deptno) values('inthiyaaz',2000,10)

insert into dupemp(name,salary,deptno) values('inthiyaaz',2000,10)

insert into dupemp(name,salary,deptno) values('Khaja',3000,20)

insert into dupemp(name,salary,deptno) values('Khaja',3000,20)

insert into dupemp(name,salary,deptno) values('Naseeb',3000,20)

Query)
with CTE as(select row_number() over(partition by name order by name) row,name,salary,deptno from dupemp )
delete from CTE where row>1

5 row's will be affected

Select * from dupemp


OutPut:-

Name      Salary   deptno

inthiyaaz  2000        10
Khaja       3000         20
Naseeb    3000       20


     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend