salix/db/versions/11346-yellowPhormium/00-address.sql

ALTER TABLE vn.address
	ADD geoFk int(11) DEFAULT NULL NULL AFTER isLogifloraAllowed,
	ADD CONSTRAINT address_zoneGeo_FK FOREIGN KEY (geoFk)
		REFERENCES vn.zoneGeo(id) ON DELETE RESTRICT ON UPDATE CASCADE;

CREATE OR REPLACE TEMPORARY TABLE tmp.tAddressGeo
	(PRIMARY KEY (id))
	ENGINE = MEMORY
	SELECT a.id, p.geoFk
		FROM vn.address a
			JOIN vn.town t ON t.provinceFk = a.provinceFk
			JOIN vn.postCode p ON p.townFk = t.id
				AND (p.code = a.postalCode OR a.postalCode IS NULL)
			JOIN vn.zoneGeo zg ON zg.id = p.geoFk
		GROUP BY a.id
		ORDER BY (a.city SOUNDS LIKE t.`name`) DESC,
			(p.code = a.postalCode) DESC;

UPDATE vn.address a
		JOIN tmp.tAddressGeo tag ON tag.id = a.id
	SET a.geoFk = tag.geoFk;

DROP TEMPORARY TABLE tmp.tAddressGeo;