How to Delete duplicate rows from a table?

Supppose we have a table without primary key and duplicate records get entered.The problem arises when you have two identical rows in the table and there is no way to distinguish between the two rows.then how do you delete the duplicate record?

Solution

You have a table named tblEmp with Duplicate EmpIds

EmpID
Manpreet
Manpreet
Manpreet
Sandeep
Sandeep
Sandeep
Sandeep
Vikas
Vikas

You can Delete duplicate records with Top Command

DELETE TOP (SELECT COUNT(*) -1 FROM dbo.tblEmp WHERE EmpID = 'Manpreet')
FROM dbo.tblEmp
WHERE EmpID = 'Manpreet'

Comments

URL Rewritting

http://www.simple-talk.com/dotnet/asp.net/a-complete-url-rewriting-solution-for-asp.net-2.0/ http://msdn.microsoft.com/en-us/library/ms972974.aspx URL Rewriting in ASP.NET Summary: Examines how to perform dynamic URL rewriting with Microsoft ASP.NET. URL rewriting is the process of intercepting an incoming Web request and automatically redirecting it to a different URL. Discusses the various techniques for implementing URL rewriting, and examines real-world scenarios of URL rewriting. (31 printed pages) Download the source code for this article. Contents Introduction Common Uses of URL Rewriting What Happens When a Request Reaches IIS Implementing URL Rewriting Building a URL Rewriting Engine Performing Simple URL Rewriting with the URL Rewriting Engine Creating Truly "Hackable" URLs Conclusion Related Books Introduction Take a moment to look at some of the URLs on your website. Do you find URLs like http://yoursite.com/info/dispEmployeeInfo.aspx?EmpID=459-099&type=summ...

SEND A PDF FILE AS AN ATTACHEMENT OF MAIL!

System.Net.Mail.MailMessage m1 = new System.Net.Mail.MailMessage(); m1.From = "manpreet@gmail.com" m1.Subject = "Test mail "; m1.Body = str.ToString(); m1.IsBodyHtml = true; m1.To.Add("jasdeep@gmail.com"); m1.CC.Add("jasdeep123@gmail.com"); m1.Attachments.Add(new Attachment(strserverpath + @"\pdf\" + PdfFileName)); smtp.Send(m1);

Sql Server Tips

IF WE WANT TO SELECT TOP N ROWS FROM A TABLE , WE USE THE FETCH NEXT SELECT * FROM table1 ---//First m rows ignore first n rows ORDER BY id OFFSET n ROWS FETCH NEXT m ROWS ONLY if n=0, m=10 then first 10 rows if n=10, m=10 then first 10 rows start from 11th row Row_number() SELECT ROW_NUMBER() OVER(order by db.id) Insertion Simultaneouly in temp table with output clause DECLARE @table1 table ( Id int, name nvarchar(50) ); DECLARE @table2 table ( Id int, name nvarchar(50) ); INSERT INTO @table2 OUTPUT INSERTED.* INTO @table1 select top 10 id, name from FinalTable SELECT * FROM @table2; SELECT * FROM @table1;

Asp.net

Search This Blog