MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE categories (`cat_id` INTEGER, `cat_title` VARCHAR(5));
INSERT INTO categories (`cat_id`, `cat_title`) VALUES
  ('1', 'green'),
  ('2', 'red'),
  ('3', 'blue'),
  ('4', 'green'),
  ('5', 'green'),
  ('6', 'red'),
  ('7', 'white');
CREATE TABLE tagging (`tagging_id` INTEGER, `tagging_record_id` INTEGER, `tagging_cat_id` INTEGER);
INSERT INTO tagging (`tagging_id`, `tagging_record_id`, `tagging_cat_id`) VALUES
  ('1', '1', '1'),
  ('2', '1', '2'),
  ('3', '2', '7'),
  ('4', '3', '5'),
  ('5', '4', '6'),
  ('6', '5', '4'),
  ('7', '5', '3'),
  ('8', '6', '5');
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
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
SELECT * FROM categories


cat_id
cat_title


1
green

2
red

3
blue

7
white


SELECT * FROM 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