0

I just bumped into this topic;

Delete all Duplicate Rows except for One in MySQL?

This almost fits my needs. But I have 2 tables which contain some (linked) duplicate rows.

The first, main table is "messages". First, before I delete anything, I would like to select them:

SELECT * FROM `messages` n1, `messages` n2 WHERE n1.id > n2.id AND n1.datetime = n2.datetime AND n1.message = n2.message

Secondly, I have the table "labels" in whoch the rows have their own id, but also a link towards the first table, which is msgid. So, messages.id = labels.msgid

The mentioned query finds all duplicates. How to show also the linked rows in the second table?

Peter Hunt
  • 1
  • 1

2 Answers2

1

If you want to delete the duplicate rows from both tables and keep only the rows with the minimum id in messages you should join labels to your query but link it only to the duplicate rows which belong to the aliased table n1.
So to select all duplicate rows that must be deleted from both tables:

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;

and to delete the duplicates from both tables:

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;

See a simplified demo.

forpas
  • 160,666
  • 10
  • 38
  • 76
0

Add the other table to the join. You'll need to join twice to get the label of both messages in the pair.

SELECT *
FROM messages AS m1
JOIN messages AS m2 ON m1.id > m2.id AND m1.datetime = m2.datetime
JOIN labels AS l1 ON l1.msgid = m1.id
JOIN labels AS l2 ON l2.msgid = m2.id
Barmar
  • 741,623
  • 53
  • 500
  • 612