Mariadb – Deleting Duplicates – mariaDB

linuxmariadb-5.5

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:

UserID|RegisterDate|UniqueID
12345|12-08-2020|ABC123
12345|11-05-2020|XYZ789
23455|20-08-2020|QWE345

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.

Database details:
mysql Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1
CentOS 1810

Out of date, yes, but that decision is taken above my pay-grade.

Best Answer

This works quite fast and 14 k isn't that much data

You should add a unique constraint on UserID so that you will never have to run it again

CREATE TABLE DeviceDetails   (
  `UserID` INTEGER,
  `RegisterDate` varchar(10),
  `UniqueID` VARCHAR(6)
);

INSERT INTO DeviceDetails  
  (`UserID`, `RegisterDate`, `UniqueID`)
VALUES
  ('12345', '12-08-2020', 'ABC123'),
  ('12345', '11-05-2020', 'XYZ789'),
  ('23455', '20-08-2020', 'QWE345');

delete d from 
DeviceDetails d
inner join (
    select max(`RegisterDate`) as maxdate, `UserID` from DeviceDetails 
    group by `UserID` 
    having count(*) > 1
) u on u.`UserID` = d.`UserID` and u.maxdate != d.`RegisterDate`;

SELECT * FROM DeviceDetails;

UserID | RegisterDate | UniqueID
-----: | :----------- | :-------
 12345 | 12-08-2020   | ABC123  
 23455 | 20-08-2020   | QWE345

db<>fiddle here

Related Question