CODEWITHSUNDEEP
sqlsql-serverduplicatesdistinctsql-delete
User987
User987

Reputation: 3823

Removing duplicates from SQL Server table

I have a situation in my SQL Server table where I have numerous duplicates of records like following:

 SID    Username                                InQueue
------------------------------------------------------------------    
 162    peeeer2  492    2017-01-18 12:20:21.820   0    354
2791    peeeer2  460    2017-01-11 00:00:00.000   1    NULL

The unwanted record here is the peeeer2 user for whom which I have set InQueue = true. I need to remove all of those duplicates where the InQueue column is set to 1 and the another criteria is that username is actually a duplicate...

The table name is SearchedUsernames:

delete from SearchedUsernames
where Username ??

Can someone help me out with this ?

Edit:

@TimSchmelter ty so much, this works like a charm. I get an error still however. I need to first drop the neighbouring FK's of this table. For example when I have a corresponding FK record in the neighbouring table called UserTransactions like following:

 ID      SID                              
----------------  
 162    162
2791    2791

I need to first drop all the records in this neighbouring table and then delete the duplicates using your query that you wrote. However, this time I'd like to drop ONLY those which HAVE duplicate records and have set InQueue = 0;

So the scenario would look like this:

  1. Drop the FK records SID from both duplicates in neighbouring table UserTransactions

  2. Then execute the query that DTV & Tim wrote with a minor change to drop only those records which are duplicate and have set InQueue = 0;

Upvotes: 2

Views: 296

Answers (3)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Probably the most intuitive solution would be:

delete s from SearchedUsernames s
where InQueue = 1 and exists(select * from SearchedUsernames
                             where InQueue = 0 and Username = s.Username)

Upvotes: 3

DVT
DVT

Reputation: 3127

WITH cte AS (
    SELECT Username, inqueue,
        ROW_NUMBER() OVER (PARTITION BY Username ORDER BY InQueue ASC) AS RN
    FROM searchedUsernames   
)
DELETE FROM cte
WHERE RN > 1;

If you afraid of more than one Inqueue=0, then use RANK

WITH cte AS (
    SELECT Username, inqueue,
        RANK() OVER (PARTITION BY Username ORDER BY InQueue ASC) AS RN
    FROM searchedUsernames   
)
DELETE FROM cte
WHERE RN > 1;

Upvotes: 5

Zohar Peled
Zohar Peled

Reputation: 82474

You could use a self join like this:

 DELETE t0
 FROM SearchedUsernames t0
 INNER JOIN SearchedUsernames t1 ON(t0.Username = t1.Username AND t0.IsQueue <> T1.IsQueue)
 WHERE AND t0.IsQueue = 1

Upvotes: 1

Related Questions