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.

Answer 1

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);