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?
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