Merge references to duplicate rows in mysql

user_613305 0
Viewed 38

This feels very simple and complex at the same time, but I can't quite get my head around an appropriate way of going about this as mysql query.

I have a table of tags called categories that should only have unique titles for the field cat_title. However, I've noticed that there are multiple rows with the same cat_title field name.

I want to delete all but the first instance of any duplicates. Simple enough, yes. But another table, tagging has a field called tagging_cat_id that references the identifier field, cat_id in the categories table. Deleting duplicates will break these references and point to nothing.

So, the more complex aspect is finding any tagging_cat_id field that references a duplicate row that's about to be deleted and change it to reference the (soon to be unique, single) first row of this cat_title

I am a novice at mysql and this is a bit out of my depth. I was almost tempted to do this manually by hand in a gui. Is there a simple enough method of doing this as a query that I could run on occasion to perform the above? (until what's causing duplicates to be created is resolved). Distrib version is 5.7.21.

Sample Data

Categories

+--------+-----------+
| cat_id | cat_title |
+--------+-----------+
|      1 | green     |
|      2 | red       |
|      3 | blue      |
|      4 | green     |
|      5 | green     |
|      6 | red       |
|      7 | white     |
+--------+-----------+

Tagging

+------------+-------------------+----------------+
| tagging_id | tagging_record_id | tagging_cat_id |
+------------+-------------------+----------------+
|          1 |                 1 |              1 |
|          2 |                 1 |              2 |
|          3 |                 2 |              7 |
|          4 |                 3 |              5 |
|          5 |                 4 |              6 |
|          6 |                 5 |              4 |
|          7 |                 5 |              3 |
|          8 |                 6 |              5 |
+------------+-------------------+----------------+

I want to convert the above to the following:

Categories

+--------+-----------+
| cat_id | cat_title |
+--------+-----------+
|      1 | green     |
|      2 | red       |
|      3 | blue      |
|      7 | white     |
+--------+-----------+

Tagging

+------------+-------------------+----------------+
| tagging_id | tagging_record_id | tagging_cat_id |
+------------+-------------------+----------------+
|          1 |                 1 |              1 |
|          2 |                 1 |              2 |
|          3 |                 2 |              7 |
|          4 |                 3 |              1 |
|          5 |                 4 |              2 |
|          6 |                 5 |              1 |
|          7 |                 5 |              3 |
|          8 |                 6 |              1 |
+------------+-------------------+----------------+
mysql
1 Answers

If your version of MySql is 8.0+ you can use this query:

SELECT cat_id, MIN(cat_id) OVER (PARTITION BY cat_title) min_id
FROM categories

to identify for each cat_id the minimum cat_id with the same cat_title so you can update the table:

WITH ids AS (
  SELECT cat_id, MIN(cat_id) OVER (PARTITION BY cat_title) min_id
  FROM categories
)
UPDATE tagging t
INNER JOIN ids i ON i.cat_id = t.tagging_cat_id
SET t.tagging_cat_id = i.min_id

Then you can delete the duplicates:

WITH ids AS (
  SELECT cat_id, MIN(cat_id) OVER (PARTITION BY cat_title) min_id
  FROM categories
)
DELETE c 
FROM categories c INNER JOIN ids i 
ON i.cat_id = c.cat_id AND i.min_id < c.cat_id

See the demo.

For previous versions of MySql that do not support window functions and CTEs:

UPDATE tagging t
INNER JOIN categories c ON c.cat_id = t.tagging_cat_id
INNER JOIN (
  SELECT cat_title, MIN(cat_id) min_id
  FROM categories 
  GROUP BY cat_title
) m ON m.cat_title = c.cat_title
SET t.tagging_cat_id = m.min_id

and:

DELETE c1 
FROM categories c1 INNER JOIN categories c2 
ON c2.cat_title = c1.cat_title
WHERE c1.cat_id > c2.cat_id

See the demo.

Results:

cat_id cat_title
1 green
2 red
3 blue
7 white

and:

tagging_id tagging_record_id tagging_cat_id
1 1 1
2 1 2
3 2 7
4 3 1
5 4 2
6 5 1
7 5 3
8 6 1
user_104988280
Related