I have a database where each row is uniquely identified by a combination of "account_id" and "device_name" (these two come together to make the row unique). There should be a maximum of two accounts with a specific "account_id", for example:
account_id: 78, device_name: "Bob",
account_id: 78, device_name: "John"
But I recently introduced a bug where there could be more than 2, and I want to LEAVE all occurrences like the one above where there's only 1 or 2, but DELETE all the ones where there's 3 or more (the bug is now rectified and the next time the user logs in it'll repair it automatically, so there's no concern).
For instance this is bad and I want to delete all 3 rows:
account_id: 39, device_name: "Tim",
account_id: 39, device_name: "Rob",
account_id: 39, device_name: "Sam"
I know I can do something like: SELECT COUNT(*) FROM accounts where account_id=89; and it'll return like count: 6. At this point I'll know that's a bug and I should delete the rows matching that ID.
I can check and do this manually for each but over a few dozen accounts it gets very repetitive. Is there a way to "chain" that with another command and say something along the lines of "find all rows in the database where the same account_id is in 3 or more rows, and delete those rows"?
You can use filtering in a delete:
delete from t
where account_id in (select account_id from t group by account_id having count(*) > 2);