# Mariadb – Deleting Duplicates – mariaDB

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.

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