Add Column Error in MySQL Transform




I'm trying to map Google's LocationCriterionServices IDs to some Geo data from Adwords. I keep getting an error in my (case when) statement, but the SQL resources I've been checking haven't shown me what I'm doing wrong. Below is my code.


Is there another way to map 1000+ values easily in MySQL that I'm missing?



alter table `iuo_geostats_1683748494`
add COLUMN `LocationName` varchar(255) as


when geo.`CityCriteriaId` = loc.`Parent ID` then loc.`Canonical Name`
else geo.`CityCriteriaId`)

from `iuo_geostats_1683748494` geo
from `adwords_locationcriterionservice_08142019` loc


  • tadashii

    Normally the add column is a one-off process and is a separate syntax from the update clause.

    From your syntax, there are two "from" and no "join" clause <- which is a clear indication of a cross join. 


    from `iuo_geostats_1683748494` geo
    from `adwords_locationcriterionservice_08142019` loc


    What I would so is a left join between the two tables and output the result to a new table instead.


    select  geo.*,
    COALESCE( loc.`Canonical Name` , geo.`CityCriteriaId`) as LocationName

    from `iuo_geostats_1683748494` geo

    left join adwords_locationcriterionservice_08142019` loc

    on geo.`CityCriteriaId` = loc.`Parent ID`