| 1 | ALTER TABLE geoloc_pays ADD COLUMN license_plate CHAR(4) DEFAULT NULL; |
| 2 | |
| 3 | CREATE TEMPORARY TABLE IF NOT EXISTS tmp_update_geoloc_pays ( |
| 4 | a2 CHAR(2) DEFAULT NULL, |
| 5 | code CHAR(4) DEFAULT NULL, |
| 6 | PRIMARY KEY(a2), |
| 7 | UNIQUE KEY(code) |
| 8 | ) CHARSET=utf8; |
| 9 | |
| 10 | INSERT IGNORE INTO tmp_update_geoloc_pays (a2, code) |
| 11 | VALUES ('AF', 'AFG'), |
| 12 | ('ZA', 'ZA'), |
| 13 | ('AL', 'AL'), |
| 14 | ('DZ', 'DZ'), |
| 15 | ('DE', 'D'), |
| 16 | ('AD', 'AND'), |
| 17 | ('AO', 'ANG'), |
| 18 | ('AG', 'AG'), |
| 19 | ('AN', 'NA'), |
| 20 | ('SA', 'KSA'), |
| 21 | ('AR', 'RA'), |
| 22 | ('AM', 'ARM'), |
| 23 | ('AU', 'AUS'), |
| 24 | ('AT', 'A'), |
| 25 | ('AZ', 'AZ'), |
| 26 | ('BS', 'BS'), |
| 27 | ('BH', 'BRN'), |
| 28 | ('BD', 'BD'), |
| 29 | ('BB', 'BDS'), |
| 30 | ('BY', 'BY'), |
| 31 | ('BE', 'B'), |
| 32 | ('BZ', 'BZ'), |
| 33 | ('BJ', 'DY'), |
| 34 | ('BM', ''), |
| 35 | ('BT', 'BHT'), |
| 36 | ('BO', 'BOL'), |
| 37 | ('BA', 'BIH'), |
| 38 | ('BW', 'RB'), |
| 39 | ('BR', 'BR'), |
| 40 | ('BN', 'BRU'), |
| 41 | ('BG', 'BG'), |
| 42 | ('BF', 'BF'), |
| 43 | ('BI', 'BU'), |
| 44 | ('KH', 'K'), |
| 45 | ('CM', 'CAM'), |
| 46 | ('CA', 'CDN'), |
| 47 | ('CV', 'CV'), |
| 48 | ('CF', 'RCA'), |
| 49 | ('CG', 'CGO'), |
| 50 | ('CL', 'RCH'), |
| 51 | ('CN', 'CN'), |
| 52 | ('CY', 'CY'), |
| 53 | ('CO', 'CO'), |
| 54 | ('KM', 'COM'), |
| 55 | ('CG', 'RCB'), |
| 56 | ('CD', 'CD'), |
| 57 | ('KR', 'ROK'), |
| 58 | ('KP', 'DVRK'), |
| 59 | ('CR', 'CR'), |
| 60 | ('CI', 'CI'), |
| 61 | ('HR', 'HR'), |
| 62 | ('CU', 'C'), |
| 63 | ('DK', 'DK'), |
| 64 | ('DJ', 'DJI'), |
| 65 | ('DO', 'DOM'), |
| 66 | ('DM', 'WD'), |
| 67 | ('EG', 'ET'), |
| 68 | ('SV', 'ES'), |
| 69 | ('AE', 'UAE'), |
| 70 | ('EC', 'EC'), |
| 71 | ('ER', 'ER'), |
| 72 | ('ES', 'E'), |
| 73 | ('EE', 'EST'), |
| 74 | ('US', 'USA'), |
| 75 | ('ET', 'ETH'), |
| 76 | ('FO', 'FR'), |
| 77 | ('FJ', 'FJI'), |
| 78 | ('FI', 'FIN'), |
| 79 | ('FR', 'F'), |
| 80 | ('GA', 'G'), |
| 81 | ('GM', 'WAG'), |
| 82 | ('GE', 'GE'), |
| 83 | ('GH', 'GH'), |
| 84 | ('GI', 'GBZ'), |
| 85 | ('GR', 'GR'), |
| 86 | ('GD', 'WG'), |
| 87 | ('GL', 'KN'), |
| 88 | ('GT', 'GCA'), |
| 89 | ('GN', 'RG'), |
| 90 | ('GQ', 'GQ'), |
| 91 | ('GY', 'GUY'), |
| 92 | ('HT', 'RH'), |
| 93 | ('HN', 'HN'), |
| 94 | ('HK', 'HK'), |
| 95 | ('HU', 'H'), |
| 96 | ('VG', 'BVI'), |
| 97 | ('IN', 'IND'), |
| 98 | ('ID', 'RI'), |
| 99 | ('IR', 'IR'), |
| 100 | ('IQ', 'IRQ'), |
| 101 | ('IE', 'IRL'), |
| 102 | ('IS', 'IS'), |
| 103 | ('IL', 'IL'), |
| 104 | ('IT', 'I'), |
| 105 | ('JM', 'JA'), |
| 106 | ('JP', 'J'), |
| 107 | ('JO', 'JOR'), |
| 108 | ('KZ', 'KZ'), |
| 109 | ('KE', 'EAK'), |
| 110 | ('KG', 'KS'), |
| 111 | ('KI', 'KIR'), |
| 112 | ('KW', 'KWT'), |
| 113 | ('LA', 'LAO'), |
| 114 | ('LS', 'LS'), |
| 115 | ('LV', 'LV'), |
| 116 | ('LB', 'RL'), |
| 117 | ('LR', 'LB'), |
| 118 | ('LY', 'LAR'), |
| 119 | ('LI', 'FL'), |
| 120 | ('LT', 'LT'), |
| 121 | ('LU', 'L'), |
| 122 | ('MK', 'MK'), |
| 123 | ('MG', 'RM'), |
| 124 | ('MY', 'MAL'), |
| 125 | ('MW', 'MW'), |
| 126 | ('MV', 'MV'), |
| 127 | ('ML', 'RMM'), |
| 128 | ('MT', 'M'), |
| 129 | ('MA', 'MA'), |
| 130 | ('MH', 'MH'), |
| 131 | ('MU', 'MS'), |
| 132 | ('MR', 'RIM'), |
| 133 | ('MX', 'MEX'), |
| 134 | ('FM', 'FSM'), |
| 135 | ('MD', 'MD'), |
| 136 | ('MC', 'MC'), |
| 137 | ('MN', 'MGL'), |
| 138 | ('MZ', 'MOC'), |
| 139 | ('MM', 'MYA'), |
| 140 | ('NA', 'NAM'), |
| 141 | ('NR', 'NAU'), |
| 142 | ('NP', 'NEP'), |
| 143 | ('NI', 'NIC'), |
| 144 | ('NE', 'RN'), |
| 145 | ('NG', 'WAN'), |
| 146 | ('NO', 'N'), |
| 147 | ('NZ', 'NZ'), |
| 148 | ('OM', 'OM'), |
| 149 | ('UG', 'EAU'), |
| 150 | ('UZ', 'UZ'), |
| 151 | ('PK', 'PK'), |
| 152 | ('PW', 'PAL'), |
| 153 | ('PS', 'PS'), |
| 154 | ('PA', 'PA'), |
| 155 | ('PG', 'PNG'), |
| 156 | ('PY', 'PY'), |
| 157 | ('NL', 'NL'), |
| 158 | ('PE', 'PE'), |
| 159 | ('PH', 'RP'), |
| 160 | ('PL', 'PL'), |
| 161 | ('PT', 'P'), |
| 162 | ('QA', 'Q'), |
| 163 | ('RE', 'RE'), |
| 164 | ('RO', 'RO'), |
| 165 | ('GB', 'GB'), |
| 166 | ('RU', 'RUS'), |
| 167 | ('RW', 'RWA'), |
| 168 | ('LC', 'WL'), |
| 169 | ('KN', 'SCN'), |
| 170 | ('SM', 'RSM'), |
| 171 | ('VA', 'V'), |
| 172 | ('VC', 'WV'), |
| 173 | ('SB', 'SOL'), |
| 174 | ('WS', 'WS'), |
| 175 | ('ST', 'STP'), |
| 176 | ('SN', 'SN'), |
| 177 | ('CS', 'SCG'), |
| 178 | ('SC', 'SY'), |
| 179 | ('SL', 'WAL'), |
| 180 | ('SG', 'SGP'), |
| 181 | ('SK', 'SK'), |
| 182 | ('SI', 'SLO'), |
| 183 | ('SO', 'SP'), |
| 184 | ('SD', 'SUD'), |
| 185 | ('LK', 'CL'), |
| 186 | ('SE', 'S'), |
| 187 | ('CH', 'CH'), |
| 188 | ('SR', 'SME'), |
| 189 | ('SZ', 'SD'), |
| 190 | ('SY', 'SYR'), |
| 191 | ('TJ', 'TJ'), |
| 192 | ('TW', 'RC'), |
| 193 | ('TZ', 'EAT'), |
| 194 | ('TD', 'TCH'), |
| 195 | ('CZ', 'CZ'), |
| 196 | ('TH', 'THA'), |
| 197 | ('TL', 'TL'), |
| 198 | ('TG', 'RT'), |
| 199 | ('TO', 'TO'), |
| 200 | ('TT', 'TT'), |
| 201 | ('TN', 'TN'), |
| 202 | ('TM', 'TM'), |
| 203 | ('TR', 'TR'), |
| 204 | ('TV', 'TUV'), |
| 205 | ('UA', 'UA'), |
| 206 | ('UY', 'ROU'), |
| 207 | ('VU', 'VU'), |
| 208 | ('VE', 'YV'), |
| 209 | ('VN', 'VN'), |
| 210 | ('YE', 'YAR'), |
| 211 | ('YU', 'YU'), |
| 212 | ('ZM', 'Z'), |
| 213 | ('ZW', 'ZW'); |
| 214 | |
| 215 | UPDATE geoloc_pays AS g |
| 216 | INNER JOIN tmp_update_geoloc_pays AS t ON (t.a2 = g.a2) |
| 217 | SET g.license_plate = t.code; |
| 218 | |
| 219 | -- vim:set syntax=mysql: |