User Id :    Password :      New Member   Forgot Password  
Topic: Delete duplicate values from table in sql server?
Anamika singh
23 Nov 2010 at 12:42 AM

We have two columns In a table column1 and column2 in which we have data like s1 and t1 in first row and then s2 t2 and so on. Now if one entry comes by the same way like s1 and t1 so how we can prevent to enter these values in table.

Re: Delete duplicate values from table in sql server?
Gaurav
16 Dec 2010 at 04:14 AM
CompanyName, FirstName, LastName and Address1. In other words, if the combined value of CompanyName, FirstName, LastName and Address is identical in two or more rows, remove all but one copy of the row. I have included the COUNT function in the select list to capture the number of duplicates for a given row set. We will need this value for the DELETE clause. Here is our complete DECLARE CURSOR statement:

DECLARE @Count int
DECLARE @FirstName nvarchar(50)
DECLARE @LastName nvarchar(50)

DECLARE dublicate_cursor CURSOR FAST_FORWARD FOR
SELECT FirstName, LastName, Count(*) - 1
FROM test
GROUP BY FirstName, LastName
HAVING Count(*) > 1

OPEN dublicate_cursor

FETCH NEXT FROM dublicate_cursor INTO @FirstName, @LastName, @Count

WHILE @@FETCH_STATUS = 0
BEGIN

SET ROWCOUNT @Count
DELETE FROM test WHERE FirstName = @FirstName AND LastName = @LastName
SET ROWCOUNT 0

FETCH NEXT FROM dublicate_cursor INTO @FirstName, @LastName, @Count
END

CLOSE dublicate_cursor
DEALLOCATE dublicate_cursor

Re: Delete duplicate values from table in sql server?
Ranjeet Ray
27 Jun 2011 at 10:48 PM

There is very simple in Sql Server 2005/2008, you can use for remove duplicate on the basis of two column

DELETE T From (Select Row_Number() Over(Partition By col1,col2 order By col1,col2) As RowNumber,* From table1) T Where T.RowNumber > 1

Total Replies: 2
 
Featured Text Ad

Featured Ad