cleft cleft - 1 year ago 41
MySQL Question

How To Populate One Column Based On Another Columns Data In Same Table?

I have two sets of data I joined into a single table. One was USA addresses and the other was Canadian addresses. I need the option to be able to sort by Country. So I inserted a blank country column into my table and my table now looks like this:

state | country | zip
| |
| |

I have the state and zip data but my country column is empty. Since US zip code format is 5 digits (ex. 12345) and CAN zip are formatted like (ex. G5Y 5S7) is it possible with mysql to populate the country data with some sort of...

UPDATE table
SET country = US
WHERE zip (LIKE or =) (US zip format);

And then go back and use some sort of
to populate the rest of country data with CAN?


Answer Source

You could check the length of the value in the zip column.

UPDATE table 
   set country = 'US'
 where char_length(zip) = 5;


UPDATE table 
   set country = 'CAN'
 where country is null

-- or, in one go --

UPDATE table t
   set country = 
  (case when char_length( = 5
   then 'US'
  else 'CAN'