I have a mariaDB database containing details about user devices (sort of an 'allowed device' list for VPN access). Back when the project started, there was no restriction on what devices were allowed so, everyone has 2 devices registered to their name. I've been tasked with removing the older device from the database.
This is a simplified database schema:
The requirement is that I have to delete the older device, in case the user has two devices. So in this example, deviceID XYZ789 belonging to User 12345 would need to be deleted.
What I have so far is this:
select min(RegisterDate), UserID from DeviceDetails where RegisterDate in (select RegisterDate from DeviceDetails) group by UserID having count(*) > 1;
I understand this is very convoluted, and there is definitely room for improvement, as it takes around 3 minutes to run on around 14k rows.
The next step would be to get these rows exported into a log, and then deleted.
I'm not really a DB person, but its been tasked to me as we inherited this product from the previous vendor and it just works so far.
mysql Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1
Out of date, yes, but that decision is taken above my pay-grade.