mysql - How to remove duplicate rows in parent table and leave one row with the lowest ID, and replace foreign key in child's table with this ID? I have the situation where I have three tables:

hgn_modern_sources
+----+--------------+
| id |    source    |
+----+--------------+
| 17 | Something... |
+----+--------------+

junc_modern_source_has_reference
+----+------------------+------------------------+
| id | modern_source_id | location_within_source |
+----+------------------+------------------------+
| 52 |               17 | 1, 1                   |
| 89 |               17 | 1, 1                   |
| 99 |               17 | 1, 1                   |
+----+------------------+------------------------+

hgn_picture_has_reference
+----+---------------------+-------------------+
| id | modern_reference_id | picture_link      |
+----+---------------------+--------------------+
| 45 | 89                  | /images/image1.png |
| 75 | 99                  | /images/image2.png |
+----+---------------------+--------------------+


My question is how to delete duplicates from junc_modern_source_has_reference (id = 89 and id = 99), leave one row with the lowest ID (id = 52), and replace foreign keys in hgn_picture_has_reference table (column modern_reference_id) with one left (id = 52)?

The solution can be only MySQL based, or PHP + MySQL (which I prefer more).
DROP TEMPORARY TABLE IF EXISTS tempTable; 
CREATE TEMPORARY TABLE tempTable
(id int
,minId int
PRIMARY KEY (id)
);

-- filling temporary table with list of duplicate ids 
-- and corresponding minimal id within each group of duplicates
WITH CTE AS (
SELECT id
    ,MIN(id) OVER (PARTITION BY modern_source_id, location_within_source ) AS minId
    ,ROW_NUMBER OVER (PARTITION BY modern_source_id, location_within_source ORDER BY id) AS rn
FROM junc_modern_source_has_reference
)
INSERT INTO tempTable (id, minId)
SELECT id, minId FROM CTE WHERE rn &gt; 1;

UPDATE hgn_picture_has_reference AS t1
JOIN tempTable AS t2 ON t1.id = t2.id
SET t1.modern_reference_id  = t2.minId;


DELETE t1
FROM junc_modern_source_has_reference AS t1
JOIN tempTable AS t2 ON t1.id = t2.id;

DROP TEMPORARY TABLE tempTable; 


If you use MySQL version older than 8.0 you can't use window functions and you need to modify part with CTE this way:

INSERT INTO tempTable (id, minId)
SELECT id, minId 
FROM 
    (SELECT t1.id
            ,t2.minId
    FROM junc_modern_source_has_reference AS t1
    JOIN (
        SELECT MIN(id) AS minId
            ,modern_source_id
            ,location_within_source
        FROM junc_modern_source_has_reference
        GROUP BY modern_source_id, location_within_source
        HAVING COUNT(1) &gt; 1
        ) AS t2 ON t1.modern_source_id = t2.modern_source_id 
                   AND t1.location_within_source = t2.location_within_source
                   AND t1.id &lt;&gt; t2.minId
    ) AS CTE