Sql Query for removing duplicate rows in SQL server 2008
-
Sql Query for removing duplicate rows in SQL server 2008
For exp
CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)
INSERT INTO DuplicateRcordTable
SELECT 1, 1
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 2 --duplicate
UNION ALL
SELECT 1, 3
UNION ALL
SELECT 1, 4
-
Bit tricky as you have no identifer on the rows
SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY Col1, Col2) AS RowNumber,
Col1, Col2
INTO #TEMP
FROM
DuplicateRcordTable
TRUNCATE TABLE DuplicateRcordTable
INSERT INTO DuplicateRcordTable (Col1, Col2)
SELECT Col1, Col2 FROM #TEMP
WHERE RowNumber = 1
The above will work if you can truncate the table in question.
-
As rightly pointed about Anthony, this is bit tricky as there is no identifier on the rows. Here is another way of deleting duplicate records using CTE:
WITH DelDupAS (SELECT col1,
col2,
Row_number()
OVER (
PARTITION BY Col1, Col2
ORDER BY Col1, Col2) AS RowNumber
FROM DuplicateRcordTable)
DELETE FROM DelDup
WHERE RowNumber > 1
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Lokesh Vij (1/29/2013)
As rightly pointed about Anthony, this is bit tricky as there is no identifier on the rows. Here is another way of deleting duplicate records using CTE:WITH DelDupAS (SELECT col1,
col2,
Row_number()
OVER (
PARTITION BY Col1, Col2
ORDER BY Col1, Col2) AS RowNumber
FROM DuplicateRcordTable)
DELETE FROM DelDup
WHERE RowNumber > 1
That was my original way of doing it, but then had a mental block of linking the CTE to the table.
-
anthony.green (1/29/2013)
That was my original way of doing it, but then had a mental block of linking the CTE to the table.This sometimes happens to me as well 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply