Add Column Error in MySQL Transform

user08142
user08142 ⚪️
in Magic ETL

Hi,

 

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

(
case

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

from `iuo_geostats_1683748494` geo
from `adwords_locationcriterionservice_08142019` loc

Comments

  • tadashii
    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`

     

     

     

Categories