Senator Guerra Souty original series calendar,replica hublot blue steel peach pointer collocation of rolex replica Rome digital scale, track type minute replica watches scale shows that the classical model is swiss replica watches incomparable, wearing elegant dress highlights.
mr-ponna.com

 

YOU ARE HERE: HOME Questions A table has a single column and has duplicate values How to delete all the duplicate values

Delete duplicate records

 View(s): 14976

A table has a single column and has duplicate values. How to delete all the duplicate values?

Answer 1)

Here i'm listing two best solutions.

1) Using SQL Server 2005 CTE (Common Table Expression)  

/* Delete Duplicate records */
WITH CTE (Col1,RowNum)
AS
(
SELECT Col1,
ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col1) AS RowNum
FROM DuplicateRcordTable
)

DELETE
FROM CTE
WHERE RowNum > 1

GO


2) Using ROWCOUNT and Correlated Subqueries

/* DELETE DUPLICATES ONE BY ONE LEAVING SINGLE ROW */
SET ROWCOUNT 1
SELECT 1
WHILE @@ROWCOUNT > 0
DELETE DuplicateRcordTable A1
WHERE 1 < (SELECT COUNT(*) FROM DuplicateRcordTable A2 WHERE A1.COL1 = A2.COL1)
SET ROWCOUNT 0
  Asked in:  Inooga Solutions   Expertise Level:  Experienced
  Last updated on Monday, 16 April 2012
4/5 stars (9 vote(s))

Register Login Ask Us Write to Us Help