| 1 | |
| 2 | alter table `nationalites` add column iso3166 varchar(2) not null; |
| 3 | |
| 4 | replace INTO `nationalites` VALUES (1,'Albanais','AL'); |
| 5 | replace INTO `nationalites` VALUES (2,'Algérien','DZ'); |
| 6 | replace INTO `nationalites` VALUES (3,'Allemand','DE'); |
| 7 | replace INTO `nationalites` VALUES (4,'Américain','US'); |
| 8 | replace INTO `nationalites` VALUES (5,'Argentin','AR'); |
| 9 | replace INTO `nationalites` VALUES (6,'Australien','AU'); |
| 10 | replace INTO `nationalites` VALUES (7,'Autrichien','AT'); |
| 11 | replace INTO `nationalites` VALUES (8,'Belge','BE'); |
| 12 | replace INTO `nationalites` VALUES (9,'Benin','BJ'); |
| 13 | replace INTO `nationalites` VALUES (10,'Botswanais','BW'); |
| 14 | replace INTO `nationalites` VALUES (11,'Bouthan','BT'); |
| 15 | replace INTO `nationalites` VALUES (12,'Brésilien','BR'); |
| 16 | replace INTO `nationalites` VALUES (13,'Britannique','GB'); |
| 17 | replace INTO `nationalites` VALUES (14,'Bulgare','BG'); |
| 18 | replace INTO `nationalites` VALUES (15,'Burkina Faso','BF'); |
| 19 | replace INTO `nationalites` VALUES (16,'Cambodgien','KH'); |
| 20 | replace INTO `nationalites` VALUES (17,'Camerounais','CM'); |
| 21 | replace INTO `nationalites` VALUES (18,'Canadien','CA'); |
| 22 | replace INTO `nationalites` VALUES (19,'Chilien','CL'); |
| 23 | replace INTO `nationalites` VALUES (20,'Chinois','CN'); |
| 24 | replace INTO `nationalites` VALUES (21,'Colombien','CO'); |
| 25 | replace INTO `nationalites` VALUES (22,'Coréen','KR'); |
| 26 | replace INTO `nationalites` VALUES (86,'Costaricien','CR'); |
| 27 | replace INTO `nationalites` VALUES (80,'Croate','HR'); |
| 28 | replace INTO `nationalites` VALUES (23,'Cubain','CU'); |
| 29 | replace INTO `nationalites` VALUES (24,'Danois','DK'); |
| 30 | replace INTO `nationalites` VALUES (26,'Egyptien','EG'); |
| 31 | replace INTO `nationalites` VALUES (81,'Equatorien','EC'); |
| 32 | replace INTO `nationalites` VALUES (27,'Espagnol','ES'); |
| 33 | replace INTO `nationalites` VALUES (28,'Estonien','EE'); |
| 34 | replace INTO `nationalites` VALUES (29,'Finlandais','FI'); |
| 35 | replace INTO `nationalites` VALUES (30,'Français','FR'); |
| 36 | replace INTO `nationalites` VALUES (31,'Gabonais','GA'); |
| 37 | replace INTO `nationalites` VALUES (32,'Georgien','GE'); |
| 38 | replace INTO `nationalites` VALUES (33,'Grec','GR'); |
| 39 | replace INTO `nationalites` VALUES (34,'Haïtien','HT'); |
| 40 | replace INTO `nationalites` VALUES (36,'Hong-Kong','HK'); |
| 41 | replace INTO `nationalites` VALUES (37,'Hongrois','HU'); |
| 42 | replace INTO `nationalites` VALUES (38,'Indien','IN'); |
| 43 | replace INTO `nationalites` VALUES (39,'Iranien','IR'); |
| 44 | replace INTO `nationalites` VALUES (40,'Irlandais','IE'); |
| 45 | replace INTO `nationalites` VALUES (41,'Islandais','IS'); |
| 46 | replace INTO `nationalites` VALUES (42,'Israélien','IL'); |
| 47 | replace INTO `nationalites` VALUES (43,'Italien','IT'); |
| 48 | replace INTO `nationalites` VALUES (44,'Ivoirien','CI'); |
| 49 | replace INTO `nationalites` VALUES (45,'Jamaïcain','JM'); |
| 50 | replace INTO `nationalites` VALUES (46,'Japonais','JP'); |
| 51 | replace INTO `nationalites` VALUES (47,'Kazakh','KZ'); |
| 52 | replace INTO `nationalites` VALUES (49,'Lettonien','LV'); |
| 53 | replace INTO `nationalites` VALUES (50,'Libanais','LB'); |
| 54 | replace INTO `nationalites` VALUES (51,'Lituanien','LT'); |
| 55 | replace INTO `nationalites` VALUES (52,'Luxembourgeois','LU'); |
| 56 | replace INTO `nationalites` VALUES (53,'Macédonien','MK'); |
| 57 | replace INTO `nationalites` VALUES (82,'Malgache','MG'); |
| 58 | replace INTO `nationalites` VALUES (54,'Malien','ML'); |
| 59 | replace INTO `nationalites` VALUES (55,'Marocain','MA'); |
| 60 | replace INTO `nationalites` VALUES (56,'Mexicain','MX'); |
| 61 | replace INTO `nationalites` VALUES (35,'Néerlandais','NL'); |
| 62 | replace INTO `nationalites` VALUES (57,'Néo-Zélandais','NZ'); |
| 63 | replace INTO `nationalites` VALUES (58,'Norvégien','NO'); |
| 64 | replace INTO `nationalites` VALUES (59,'Palestinien','PS'); |
| 65 | replace INTO `nationalites` VALUES (60,'Péruvien','PE'); |
| 66 | replace INTO `nationalites` VALUES (61,'Polonais','PL'); |
| 67 | replace INTO `nationalites` VALUES (62,'Portugais','PT'); |
| 68 | replace INTO `nationalites` VALUES (63,'Roumain','RO'); |
| 69 | replace INTO `nationalites` VALUES (64,'Russe','RU'); |
| 70 | replace INTO `nationalites` VALUES (65,'Sénégalais','SN'); |
| 71 | replace INTO `nationalites` VALUES (66,'Serbe','CS'); |
| 72 | replace INTO `nationalites` VALUES (84,'Singapourien','SG'); |
| 73 | replace INTO `nationalites` VALUES (67,'Slovaque','SI'); |
| 74 | replace INTO `nationalites` VALUES (69,'Sud-Africain','ZA'); |
| 75 | replace INTO `nationalites` VALUES (70,'Suédois','SE'); |
| 76 | replace INTO `nationalites` VALUES (71,'Suisse','CH'); |
| 77 | replace INTO `nationalites` VALUES (72,'Tadjik','TJ'); |
| 78 | replace INTO `nationalites` VALUES (73,'Taïwanais','TW'); |
| 79 | replace INTO `nationalites` VALUES (74,'Tchèque','CZ'); |
| 80 | replace INTO `nationalites` VALUES (75,'Tunisien','TN'); |
| 81 | replace INTO `nationalites` VALUES (76,'Turc','TR'); |
| 82 | replace INTO `nationalites` VALUES (83,'Ukrainien','UA'); |
| 83 | replace INTO `nationalites` VALUES (85,'Uruguayen','UY'); |
| 84 | replace INTO `nationalites` VALUES (77,'Vénézuélien','VE'); |
| 85 | replace INTO `nationalites` VALUES (78,'Vietnamien','VN'); |
| 86 | replace INTO `nationalites` VALUES (79,'Yougoslave','CS'); |
| 87 | |
| 88 | alter table geoloc_pays add column nat varchar(100) not null; |
| 89 | update geoloc_pays inner join nationalites ON iso3166=a2 set nat=text; |
| 90 | update auth_user_md5 set nationalite='' where nationalite='0' or nationalite='36'; |
| 91 | alter table auth_user_md5 change column nationalite nationalite varchar(2) not null; |
| 92 | update auth_user_md5 AS u |
| 93 | inner join nationalites AS n ON u.nationalite=n.id |
| 94 | inner join geoloc_pays AS g ON iso3166=a2 |
| 95 | set u.nationalite = a2; |
| 96 | |
| 97 | drop table nationalites; |