Delete few duplicate rows from table in sql

Question

I got a list of around 5 users which have duplicate entries in the table. Here is the query i used to get the duplicate entries. Please suggest some easy way to delete few entries.

USE [DB]

SELECT UserName, COUNT(*) AS CountOF 
FROM UserDetails
WHERE RoleID = '2'
GROUP BY UserName
HAVING COUNT(*) > 1

Are you sure you are using MySQL (and not SQL Server)? What is the structure of the table? — Gordon Linoff
– Gordon Linoff, Commented May 26, 2017 at 2:49

Gordon Linoff · Accepted Answer · 2017-05-26 02:56:26Z

2

The use of square braces suggests SQL Server. If so, you can do:

WITH todelete as (
      SELECT ud.*,
             ROW_NUMBER() OVER (PARTITION BY RoleId, username ORDER BY RoleId) as seqnum
      FROM UserDetails
      WHERE RoleID = 2
     )
DELETE FROM todelete
    WHERE seqnum > 1;

Note: If RoleId is a number, then don't compare the value to a string.

edited May 26, 2017 at 2:56

answered May 26, 2017 at 2:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

beginner Over a year ago

yes role id is a number. If there are 2 duplicate entries , the above query will delete one right ?

Gordon Linoff Over a year ago

This logic will keep one value for each username for RoleId = 2.

Invalid · Accepted Answer · 2017-05-26 03:08:22Z

0

need another uniq column like id or createtime more database support

with repeatData as (
    SELECT UserName,COUNT(*) as CountOF,min(id) as keepTag FROM UserDetails
    WHERE RoleID = '2'
    GROUP BY UserName
    HAVING COUNT(*)>1),
toDelData as (
    select id from UserDetails 
    where UserName in (SELECT UserName from repeatData) 
    and id not in (SELECT keepTag from repeatData) )
delete FROM UserDetails where id in (select id from toDelData )

answered May 26, 2017 at 3:08

Invalid

661 silver badge5 bronze badges

Collectives™ on Stack Overflow

Delete few duplicate rows from table in sql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related