MySQL Delete Distinct - IE Keep any duplicate delete unique

Hi all first time poster but have had some great ideas from people on this platform before. Just learning and starting to use MySQL for large database and have found an issue I cannot find an answer for on how to do this.

I have a table structure as below:

|dt|ID|Location ID|
|--|--|-----------|
|21/03/2021 10:57:12|ab123|1234|
|21/03/2021 10:57:12|ab127|19826|
|21/03/2021 10:57:12|ab123|1721|
|21/03/2021 10:57:12|ab124|7656|
|21/03/2021 10:57:12|ab130|1001|
|21/03/2021 10:57:12|ab335|1991|
|21/03/2021 10:57:12|ab123|0010|

What I would like to do is remove all entries that have a single ID that matches. In essence delete any distinct ID's.

So this would return:

|dt|ID|Location ID|
|--|--|-----------|
|21/03/2021 10:57:12|ab123|1234|
|21/03/2021 10:57:12|ab123|1721|
|21/03/2021 10:57:12|ab123|0010|

The command DELETE(Distinct 'ID') from table; would be ideally what I want to do but this isn't valid?

Solution

Use a subselect to get the ID that should be deleted

CREATE TABLE table1
    (`dt` varchar(19), `ID` varchar(5), `Location ID` int)
;
    
INSERT INTO table1
    (`dt`, `ID`, `Location ID`)
VALUES
    ('21/03/2021 10:57:12', 'ab123', 1234),
    ('21/03/2021 10:57:12', 'ab127', 19826),
    ('21/03/2021 10:57:12', 'ab123', 1721),
    ('21/03/2021 10:57:12', 'ab124', 7656),
    ('21/03/2021 10:57:12', 'ab130', 1001),
    ('21/03/2021 10:57:12', 'ab335', 1991),
    ('21/03/2021 10:57:12', 'ab123', 0010)
;

DELETE FROM table1 WHERE ID IN (seLECt `ID` FROM (SELECT ID FROM table1) t1  GROUP BY ID HAVING COUNT(*) = 1)

SELECT * FROM table1

dt                  | ID    | Location ID
:------------------ | :---- | ----------:
21/03/2021 10:57:12 | ab123 |        1234
21/03/2021 10:57:12 | ab123 |        1721
21/03/2021 10:57:12 | ab123 |          10

db<>fiddle here

This version with inner join

CREATE TABLE table1
    (`dt` varchar(19), `ID` varchar(5), `Location ID` int)
;
    
INSERT INTO table1
    (`dt`, `ID`, `Location ID`)
VALUES
    ('21/03/2021 10:57:12', 'ab123', 1234),
    ('21/03/2021 10:57:12', 'ab127', 19826),
    ('21/03/2021 10:57:12', 'ab123', 1721),
    ('21/03/2021 10:57:12', 'ab124', 7656),
    ('21/03/2021 10:57:12', 'ab130', 1001),
    ('21/03/2021 10:57:12', 'ab335', 1991),
    ('21/03/2021 10:57:12', 'ab123', 0010)
;

DELETE table1 FROM table1
        INNER JOIN  (seLECt `ID` FROM (SELECT ID FROM table1) t1  GROUP BY ID HAVING COUNT(*) = 1) t1
     ON table1.ID = t1.ID 
;

SELECT * FROM table1

dt                  | ID    | Location ID
:------------------ | :---- | ----------:
21/03/2021 10:57:12 | ab123 |        1234
21/03/2021 10:57:12 | ab123 |        1721
21/03/2021 10:57:12 | ab123 |          10

db<>fiddle here