INDEX countryId (countryId)
) ENGINE=InnoDB, CHARSET=utf8;
-INSERT INTO profile_addresses (pid, id, postalCode, updateTime, pub, comment, latitude, longitude, countryId,
- type, flags)
- SELECT uid, adrid, postcode, datemaj, pub, NULL, glat, glng, country,
+INSERT INTO profile_addresses (pid, id, postalCode, updateTime, pub, comment, latitude, longitude,
+ countryId, type, flags)
+ SELECT uid, adrid, postcode, datemaj, pub, NULL, glat, glng, IF(country = '' OR country = '00', NULL, country),
IF(FIND_IN_SET('pro', 'statut'), 'job', 'home'),
- CONCAT(IF(FIND_IN_SET('res-secondaire', 'statut'), 'secondary,', ''),
- IF(FIND_IN_SET('courrier', 'statut'), 'mail,', ''),
- IF(FIND_IN_SET('active', 'statut'), 'current,', ''),
- IF(FIND_IN_SET('temporaire', 'statut'), 'temporary', ''))
+ CONCAT(IF(FIND_IN_SET('res-secondaire', statut), 'secondary,', ''),
+ IF(FIND_IN_SET('courrier', statut), 'mail,', ''),
+ IF(FIND_IN_SET('active', statut), 'current,', ''),
+ IF(FIND_IN_SET('temporaire', statut), 'temporary', ''))
FROM #x4dat#.adresses;
+INSERT INTO profile_addresses (pid, jobid, id, postalCode, pub, comment,
+ latitude, longitude, countryId, type)
+ SELECT e.uid, j.id, e.entrid, e.postcode, e.adr_pub, NULL, e.glat, e.glng,
+ IF(e.country = '' OR e.country = '00', NULL, e.country), 'job'
+ FROM #x4dat#.entreprises AS e
+ INNER JOIN profile_job_enum AS j ON (e.entreprise = j.name);
+
DROP TABLE IF EXISTS geoloc_countries;
CREATE TABLE geoloc_countries (
iso_3166_1_a2 CHAR(2) NOT NULL,
WHEN "ER" THEN 232
ELSE n3 END,
worldrgn, pays, country, capital, nat,
- phoneprf, phoneformat, t.code
+ phoneprf, '', t.code
FROM #x4dat#.geoloc_pays AS g
INNER JOIN tmp_update_geoloc_pays AS t ON (t.a2 = g.a2);