Some examples of MySQL Syntax

These are here purely to jog my own memory as to how I did things. Probably not much use to anyone else
--2008-02-06
--added new fields: mast_contacts.firm_num, mast_contacts.no_office, mast_firm.dup_branch
--added new indexes on: mast_firm.master_id_tag, mast_firm.office_id (UNIQUE)

SELECT * FROM mast_contacts c JOIN mast_firm f ON f.office_id = c.office_id
WHERE firm_num is null ORDER BY f.office_id;

UPDATE mast_contacts c JOIN mast_firm f ON f.office_id = c.office_id
SET c.firm_num = f.mast_firm_num
WHERE dup_branch= -1 AND c.city = f.city;

UPDATE mast_contacts c JOIN mast_firm f ON f.office_id = c.office_id
SET c.firm_num = f.mast_firm_num
WHERE dup_branch= 0

--replace branch id with corrected version
UPDATE mast_contacts c JOIN mast_firm f ON f.mast_firm_num = c.firm_num
SET c.office_id = f.office_id

--test with:
SELECT c.office_id, f.office_id FROM
mast_contacts c JOIN mast_firm f ON f.mast_firm_num = c.firm_num
WHERE c.office_id != f.office_id

--find bad contacts (with no valid office)
SELECT firm_num, c.* FROM mast_contacts c LEFT JOIN mast_firm f ON f.office_id = c.office_id WHERE f.office_id is NULL;

--one way of updating them
UPDATE mast_contacts c LEFT JOIN mast_firm f ON f.office_id = c.office_id
SET no_office = -1
WHERE f.office_id is NULL;

--but actually this gets a few more:
UPDATE mast_contacts c
SET no_office = -1
WHERE firm_num is null

--final tweaking to clear up about half of the bad offices
UPDATE mast_contacts c JOIN mast_firm f ON f.master_id_tag = c.id_tag_master AND f.city=c.city
SET c.office_id = f.office_id, c.no_office = 0
WHERE c.no_office = -1;