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