Sync Data Between SQL Tables
These two queries are doing a two-way comparison between the procedure or diagnostic data table (proc_united_kingdom) and the hospital table (hospitals_united_kingdom) based on the column serial. They help you find which serial numbers exist in one table but not the other.
-- Gets every unique serial from proc_norway_all
-- Checks whether that serial exists in the hospitals table
-- Returns only those that don’t appear in hospitals_norway
SELECT DISTINCT p.serial
FROM `proc_united_kingdom` AS p
WHERE p.serial NOT IN (
SELECT h.serial
FROM `hospitals_united_kingdom` AS h
);
-- Reverse comparison
SELECT DISTINCT h.serial
FROM `hospitals_united_kingdom` AS h
WHERE h.serial NOT IN (
SELECT p.serial
FROM `proc_united_kingdom` AS p
);
The following performs a safe clean up by deleting rows from one table only when they no longer have a matching record in another table:
-- Delete serials from the data table
DELETE h
FROM `0uk_procedures_all` AS h
LEFT JOIN `hospitals_united_kingdom_proc` AS p
ON p.serial = h.serial
WHERE p.serial IS NULL;
-- Delete serials from the hospital table
DELETE h
FROM `hospitals_united_kingdom_proc` AS h
LEFT JOIN `0uk_procedures_all` AS p
ON p.serial = h.serial
WHERE p.serial IS NULL;
Topic last reviewed: 16/12/25