MySQL 5.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE messages(id int, datetime datetime, message varchar(100));
INSERT INTO messages(id, datetime, message) VALUES
(1, '2020-01-01', 'a'), (2, '2020-01-02', 'b'), (3, '2020-01-01', 'a'),
(4, '2020-01-02', 'b'), (5, '2020-01-01', 'b'), (6, '2020-01-02', 'a');
SELECT * FROM messages;


id
datetime
message


1
2020-01-01 00:00:00
a

2
2020-01-02 00:00:00
b

3
2020-01-01 00:00:00
a

4
2020-01-02 00:00:00
b

5
2020-01-01 00:00:00
b

6
2020-01-02 00:00:00
a


CREATE TABLE labels(id int, msgid int);
INSERT INTO labels(id, msgid) VALUES
(1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6);
SELECT * FROM labels;


id
msgid


1
1

2
2

3
3

4
4

5
5

6
6


SELECT n1.*, l.*
FROM messages n1 
INNER JOIN messages n2 
ON n1.id > n2.id AND n1.datetime = n2.datetime AND n1.message = n2.message
INNER JOIN labels l
ON l.msgid = n1.id;


id
datetime
message
id
msgid


3
2020-01-01 00:00:00
a
3
3

4
2020-01-02 00:00:00
b
4
4


DELETE n1, l 
FROM messages n1 
INNER JOIN messages n2 
ON n1.id > n2.id AND n1.datetime = n2.datetime AND n1.message = n2.message
INNER JOIN labels l
ON l.msgid = n1.id;
SELECT * FROM messages;


id
datetime
message


1
2020-01-01 00:00:00
a

2
2020-01-02 00:00:00
b

5
2020-01-01 00:00:00
b

6
2020-01-02 00:00:00
a


SELECT * FROM labels;


id
msgid


1
1

2
2

5
5

6
6

id	datetime	message
1	2020-01-01 00:00:00	a
2	2020-01-02 00:00:00	b
3	2020-01-01 00:00:00	a
4	2020-01-02 00:00:00	b
5	2020-01-01 00:00:00	b
6	2020-01-02 00:00:00	a