-- 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');