Commit | Line | Data |
---|---|---|
b8ed94e9 SJ |
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: |