Geek Answers Handbook

Removing duplicate records from a table

I have a table called Table1 that contains 48 records. Of these, there should be only 24 in this table. For some reason, I received duplicate entries. How to remove duplicate entries from this table.

+5
source share
3 answers

Here you can try if the version of SQL Server is 2005 or later.

WITH cte AS
    (
    SELECT {list-of-columns-in-table},
      row_number() over (PARTITION BY {list-of-key-columns} ORDER BY {rule-to-determine-row-to-keep}) as sequence
    FROM myTable
    )

DELETE FROM cte
WHERE sequence > 1

This uses a common table expression (CTE) and adds a column of sequences. {list-of-columns-in-table} is exactly as indicated. Not all columns are needed, but I will not explain here.

{list-of-key-columns] are the columns that you use to determine what the duplicate is.

{rule-to-define-row-to-keep} - , - , . , , .

.

WITH cte AS
    (
    SELECT ID, CourseName, DateAdded,
        row_number() over (PARTITION BY CourseName ORDER BY DateAdded) as sequence
    FROM Courses
    )

DELETE FROM cte
WHERE sequence > 1

CoursName DateAdded.

+5

http://support.microsoft.com/kb/139444

- . , .;)

, . , , .

, , , , , HAVING COUNT (*) > 1. .

0

This is an easier way.

Select * Into #TempTable FROM  YourTable
Truncate Table YourTable
Insert into YourTable Select Distinct * from #TempTable
Drop Table #TempTable
0
source

More articles:


All Articles