Identifying duplicates rows in an SQLite3 database and deleting them

Question

I have a table listed_users that has two TEXT columns, code and username:

code | username
-----|---------
aa   | john_doe
ab   | jane_doe
ca   | john_doe
ca   | john_doe <-- duplicate
da   | ryan_doe

I'd like to write a command that will delete duplicates such as ca | john_doe, where the same information appears in both columns in more than one row.

You should have checked the existence of the row before adding it again... Can the code column be the same, but with a different user? — OneCricketeer
– OneCricketeer, Commented Jan 7, 2018 at 0:04
Roughly to find the duplicate rows the query should be something like: select code, username from listed_users group by code, username having count(1) > 1 — Michael Butscher
– Michael Butscher, Commented Jan 7, 2018 at 0:09
@cricket_007 Yeah, a user can have more than one code listed. — John Smith
– John Smith, Commented Jan 7, 2018 at 1:10

CL. · Accepted Answer · 2018-01-07 10:04:35Z

8

To delete one of a pair of duplicate rows, you must have some mechanism to identify it. In SQLite, this would be the rowid.

The following query returns the rowid values of all the rows you want to keep, i.e., one row for each unique code/name combination:

SELECT min(rowid)
FROM listed_users
GROUP BY code, username;

You want to delete all rows not in that list:

DELETE FROM listed_users
WHERE rowid NOT IN (SELECT min(rowid)
                    FROM listed_users
                    GROUP BY code, username);

answered Jan 7, 2018 at 10:04

CL.

182k18 gold badges241 silver badges282 bronze badges

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

Collectives™ on Stack Overflow

Identifying duplicates rows in an SQLite3 database and deleting them

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related