Updating Brand New SQL Table

WS_CHI
WS_CHI Member
in Magic ETL

What I'm trying to do with this sql data flow is to add to my data two additional columns based on one of the metrics in the origial input. Specifically, take DMA data and get a state and region column. But I'm running into issues.

 

I planned to do this by editing the original data, create a new table with the desired columns, and then full outer join the tables. The first transform updates a column in the source. That's fine. But I'm having trouble with my new table. Below are a few attempts to get things to work based on browsing forms. The bolded lines are where Domo tells me I'm getting errors. 

 

V1
Create Table blog_dma_state_region
Select RIGHT(`Metro`, 2) AS State, `Metro`

Alter Table blog_dma_state_region 
ADD US_Region varchar(255)

INSERT INTO blog_dma_state_region (US_Region)
VALUE ("Unknown")

V2 
Create Table blog_dma_state_region
Select RIGHT(`ga_blog_dma_agg`.`Metro`, 2) AS State, `Metro`

Alter Table blog_dma_state_region 
ADD US_Region Varchar(250) DEFAULT 'Unknown' NOT NULL 

V3
Create Table blog_dma_state_region
Select RIGHT(`ga_blog_dma_agg`.`Metro`, 2) AS State, US_Region, `Metro`

UPDATE blog_dma_state_region
Set US_Region = CASE WHEN IN ('TX','OK','AZ','NM') Then 'Southwest'
.
.
.

The errors are all the same - my syntax. Which is getting pretty frustrating because I don't know how UPDATE (table) is a syntax error. And because it's getting caught at the same place (despite successfully updating my input in a different transform for this flow), I'm wondering if the issue here is because I'm creating a new table and then trying to edit it in the next step.

 

Is this even the right way to go about things? Should my CASE WHEN be in the CREATE NEW TABLE portion? And if so, how would that look? Do I need to split this? Create the table, generate an output, and then in a different transform update it? 

 

 

 

Best Answer

  • cwolman
    cwolman Contributor
    Answer ✓

    Table changes such as adding fields and indexes must be done in their own transforms and you need to uncheck the box for 'Generate Output Table'.  The other way to accomplish the table creations and alters would be to create a procedure and then call that procedure from another transform.

     

    If I understand what you are trying to accomplish may I recommend the mySQL transform below which does not require the creation of another table to join.

     

    select right(`Metro`, 2) as State

        , `Metro`

        , case when right(`Metro`, 2) in('TX','OK','AZ','NM') then 'Southwest'

                   when right(`Metro`, 2) in('PA','NJ','NY') then 'Northeast'

                   else 'Unknown'

          end as US_Region
    from `ga_blog_dma_agg


    -----------------
    Chris

Answers

  • cwolman
    cwolman Contributor
    Answer ✓

    Table changes such as adding fields and indexes must be done in their own transforms and you need to uncheck the box for 'Generate Output Table'.  The other way to accomplish the table creations and alters would be to create a procedure and then call that procedure from another transform.

     

    If I understand what you are trying to accomplish may I recommend the mySQL transform below which does not require the creation of another table to join.

     

    select right(`Metro`, 2) as State

        , `Metro`

        , case when right(`Metro`, 2) in('TX','OK','AZ','NM') then 'Southwest'

                   when right(`Metro`, 2) in('PA','NJ','NY') then 'Northeast'

                   else 'Unknown'

          end as US_Region
    from `ga_blog_dma_agg


    -----------------
    Chris
  • WS_CHI
    WS_CHI Member

    Thanks, cwolman!

     

    Knowing those changes need to be seperate is helpful, and your code below simplify things so thanks for that!

     

     

     

     

Categories