salix/db/versions/11346-yellowPhormium/01-client.sql

ALTER TABLE vn.client
	CHANGE hasDailyInvoice hasDailyInvoice tinyint(1) DEFAULT 0 NOT NULL
		COMMENT 'Indica si el cliente requiere facturación diaria por defecto se copiará lo que tenga country.hasDailyInvoice'
		AFTER recommendedCredit,
	ADD geoFk int(11) DEFAULT NULL NULL AFTER hasDailyInvoice,
	ADD CONSTRAINT client_zoneGeo_FK FOREIGN KEY (geoFk)
		REFERENCES vn.zoneGeo(id) ON DELETE RESTRICT ON UPDATE CASCADE;

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

UPDATE vn.client c
		JOIN tmp.tClientGeo tcg ON tcg.id = c.id
	SET c.geoFk = tcg.geoFk;

DROP TEMPORARY TABLE tmp.tClientGeo;