mysql - Setting many rows new field to a specific value based on another field in one query

Question

I'm adding a new column to my CountryCodes table, phoneCodes. I want to add each country's phone code based on the numericCode that already exists in the table. Is there a way to do this without doing a SET for each country? It seems inefficient to do:

UPDATE TABLE `CountryCodes`
SET `phoneCode` = 1
WHERE `numericCode = 840
...
/* Do this for each country?*/

question from:https://stackoverflow.com/questions/65890007/setting-many-rows-new-field-to-a-specific-value-based-on-another-field-in-one-qu

Answer 1

You can use a case expression:

update table countrycodes
    set phonecode = (case when numericCode = 840 then 1
                          . . .
                     end)
    where numericCode in (840, . . .);