MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- тестовые исходные данные
CREATE TABLE main (id_main INT PRIMARY KEY, val INT);
INSERT INTO main VALUES (1,1), (2,2), (3,1), (4,3);
CREATE TABLE slave (id_slave1 INT, id_main INT, val INT,
                     FOREIGN KEY (id_main) REFERENCES main (id_main));
INSERT INTO slave VALUES 
(1,1,11), (2,2,22), (3,3,33), (4,4,44);

Records: 4  Duplicates: 0  Warnings: 0



Records: 4  Duplicates: 0  Warnings: 0


-- так выглядят связанные данные
SELECT main.val mval, slave.val s1val
FROM main
JOIN slave USING (id_main)
ORDER by 1,2;


mval
s1val


1
11

1
33

2
22

3
44


-- обновляем связанную таблицу, перекидываем связти дубликатов на минимальную запись
SELECT * FROM slave;

UPDATE slave
JOIN ( SELECT t1.id_main, MIN(t2.id_main) min_id_main
       FROM main t1
       JOIN main t2 USING (val)
       GROUP BY t1.id_main ) t3 USING (id_main)
SET slave.id_main = t3.min_id_main;

SELECT * FROM slave;


id_slave1
id_main
val


1
1
11

2
2
22

3
3
33

4
4
44



Rows matched: 4  Changed: 1  Warnings: 0




id_slave1
id_main
val


1
1
11

2
2
22

3
1
33

4
4
44


-- уждаляем лишние записи из основной таблицы
SELECT * FROM main;

DELETE t1
FROM main t1
JOIN main t2 using (val)
WHERE t1.id_main > t2.id_main;

SELECT * FROM main;


id_main
val


1
1

2
2

3
1

4
3




id_main
val


1
1

2
2

4
3


-- проверяем, что связи не потерялись
SELECT main.val mval, slave.val s1val
FROM main
JOIN slave USING (id_main)
ORDER by 1,2;


mval
s1val


1
11

1
33

2
22

3
44