1

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.

2 Answers 2

1

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

1
  • 2 devices are allowed due to business requirements, but now they want the older one deleted periodically. This works perfect!
    – John Doe
    Sep 29, 2020 at 10:35
0

The other common approach for duplicates removal is to JOIN the table on itself:

DELETE w.* 
  FROM DeviceDetails AS w
  JOIN DeviceDetails AS z ON z.UserID = w.UserID
                         AND z.RegisterDate > w.RegisterDate
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.