DELETE jobillicoCA_jobs_fixed

FROM jobillicoCA_jobs_fixed

JOIN ( SELECT id, ROW_NUMBER() OVER (PARTITION BY jobillicoCA_id ORDER BY id ASC) rn

       FROM jobillicoCA_jobs_fixed

       ) AS cte USING (id)

WHERE cte.rn > 1;

DELETE FROM jobillicoCA_jobs_fixed

WHERE id NOT IN (

    SELECT MIN(id)

    FROM jobillicoCA_jobs_fixed

    GROUP BY jobillicoCA_id

);

CREATE TEMPORARY TABLE temp_duplicates AS

SELECT j1.*

FROM jobillicoCA_jobs_fixed j1

JOIN jobillicoCA_jobs_fixed j2

ON j1.jobillicoCA_id = j2.jobillicoCA_id

WHERE j1.id > j2.id;

 

DELETE j1

FROM jobillicoCA_jobs_fixed j1

JOIN temp_duplicates td ON j1.id = td.id;

DELETE j1

FROM jobillicoCA_jobs_fixed j1

INNER JOIN jobillicoCA_jobs_fixed j2

WHERE

    j1.id > j2.id AND

    j1.jobillicoCA_id = j2.jobillicoCA_id;