v4_migration.sql - mozsearch

Enable keyboard shortcuts

-- This Source Code Form is subject to the terms of the Mozilla Public

-- License, v. 2.0. If a copy of the MPL was not distributed with this

-- file, You can obtain one at http://mozilla.org/MPL/2.0/.

UPDATE addresses_data

SET address_level1 =

    CASE address_level1

    -- Canada

        WHEN 'Alberta' THEN 'AB'

        WHEN 'British Columbia' THEN 'BC'

        WHEN 'Manitoba' THEN 'MB'

        WHEN 'New Brunswick' THEN 'NB'

        WHEN 'Newfoundland and Labrador' THEN 'NL'

        WHEN 'Northwest Territories' THEN 'NT'

        WHEN 'Nova Scotia' THEN 'NS'

        WHEN 'Nunavut' THEN 'NU'

        WHEN 'Ontario' THEN 'ON'

        WHEN 'Prince Edward Island' THEN 'PE'

        WHEN 'Quebec' THEN 'QC'

        WHEN 'Saskatchewan' THEN 'SK'

        WHEN 'Yukon' THEN 'YT'

        -- US

        WHEN 'Alabama' THEN 'AL'

        WHEN 'Alaska' THEN 'AK'

        WHEN 'American Samoa' THEN 'AS'

        WHEN 'Arizona' THEN 'AZ'

        WHEN 'Arkansas' THEN 'AR'

        WHEN 'Armed Forces (AA)' THEN 'AA'

        WHEN 'Armed Forces (AE)' THEN 'AE'

        WHEN 'Armed Forces (AP)' THEN 'AP'

        WHEN 'California' THEN 'CA'

        WHEN 'Colorado' THEN 'CO'

        WHEN 'Connecticut' THEN 'CT'

        WHEN 'Delaware' THEN 'DE'

        WHEN 'District of Columbia' THEN 'DC'

        WHEN 'Florida' THEN 'FL'

        WHEN 'Georgia' THEN 'GA'

        WHEN 'Guam' THEN 'GU'

        WHEN 'Hawaii' THEN 'HI'

        WHEN 'Idaho' THEN 'ID'

        WHEN 'Illinois' THEN 'IL'

        WHEN 'Indiana' THEN 'IN'

        WHEN 'Iowa' THEN 'IA'

        WHEN 'Kansas' THEN 'KS'

        WHEN 'Kentucky' THEN 'KY'

        WHEN 'Louisiana' THEN 'LA'

        WHEN 'Maine' THEN 'ME'

        WHEN 'Marshall Islands' THEN 'MH'

        WHEN 'Maryland' THEN 'MD'

        WHEN 'Massachusetts' THEN 'MA'

        WHEN 'Michigan' THEN 'MI'

        WHEN 'Micronesia' THEN 'FM'

        WHEN 'Minnesota' THEN 'MN'

        WHEN 'Mississippi' THEN 'MS'

        WHEN 'Missouri' THEN 'MO'

        WHEN 'Montana' THEN 'MT'

        WHEN 'Nebraska' THEN 'NE'

        WHEN 'Nevada' THEN 'NV'

        WHEN 'New Hampshire' THEN 'NH'

        WHEN 'New Jersey' THEN 'NJ'

        WHEN 'New Mexico' THEN 'NM'

        WHEN 'New York' THEN 'NY'

        WHEN 'North Carolina' THEN 'NC'

        WHEN 'North Dakota' THEN 'ND'

        WHEN 'Northern Mariana Islands' THEN 'MP'

        WHEN 'Ohio' THEN 'OH'

        WHEN 'Oklahoma' THEN 'OK'

        WHEN 'Oregon' THEN 'OR'

        WHEN 'Palau' THEN 'PW'

        WHEN 'Pennsylvania' THEN 'PA'

        WHEN 'Puerto Rico' THEN 'PR'

        WHEN 'Rhode Island' THEN 'RI'

        WHEN 'South Carolina' THEN 'SC'

        WHEN 'South Dakota' THEN 'SD'

        WHEN 'Tennessee' THEN 'TN'

        WHEN 'Texas' THEN 'TX'

        WHEN 'Utah' THEN 'UT'

        WHEN 'Vermont' THEN 'VT'

        WHEN 'Virgin Islands' THEN 'VI'

        WHEN 'Virginia' THEN 'VA'

        WHEN 'Washington' THEN 'WA'

        WHEN 'West Virginia' THEN 'WV'

        WHEN 'Wisconsin' THEN 'WI'

        WHEN 'Wyoming' THEN 'WY'

        -- Keep the original value if no match is found

        ELSE address_level1

END

WHERE country IN ('US', 'CA');