CODEWITHSUNDEEP
sql-server-2008duplicates
Big Pimpin
Big Pimpin

Reputation: 437

Find Duplicates in SQL Server Table

Somehow there are duplicate entries in my table. Data structure is set-up like this:

RecordID, UserID, clockin, clockout

So if one user has 2 entries with the same clockin/clockout time then it is a duplicate. A duplicate would look like this. The entry should only exist once, and the clockin & clock out time are exactly the same.

RecordID  UserID  clockin              clockout
1         16      2015-01-12 07:15:23  2015-01-12 08:55:15
2         16      2015-01-12 07:15:23  2015-01-12 08:55:15
3         44      2014-12-18 08:02:36  2014-12-18 04:02:36
4         44      2014-12-18 08:02:36  2014-12-18 04:02:36

Upvotes: 1

Views: 45

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

With row_number window function:

with cte as(select *, row_number() over(partition by UserID, clockin, clockout 
                                        order by RecordID ) as rn from TableName)
delete from cte where rn > 1

Upvotes: 2

Zohar Peled
Zohar Peled

Reputation: 82524

First, you need to find the duplicated records and delete them, leaving only one record for each combination of UserId, clockin, clockout values.

DELETE 
FROM MyTable
WHERE RecordId IN(
    SELECT t1.RecordId
    FROM MyTable t1
    INNER JOIN MyTable t2 ON(t1.UserId = t2.UserId 
                         AND t1.clockin = t2.clockin 
                         AND t1.clockout = t2.clockout)
    WHERE t1.RecordId < t2.RecordId
)

Then you should set up a unique constraint or unique index on the combination of those 3 columns, so that Sql Server will prevent you from entering duplicated data.

Upvotes: 1

Related Questions