Snowfalke sql update if exists else insert

Question

I have looked at other question but seems that snowflake doesn't support if/else in sql, at least not the way that other sql servers support it.

some suggested to use javascript but i would like to avoid that if I can.

I am trying to Insert into a table using snowflake python library some data, if it's already there then I would like to update the data, I have looked at merge but it doesn't seem to fit me since my data isn't a table

that's what I have so far that isn't working

f"""BEGIN
                IF (EXISTS (SELECT * FROM {self.okr_table} WHERE kpi=TRUE AND Month(month)=MONTH(current_date()) AND year(month)=YEAR(current_date())))
                THEN 
                UPDATE {self.okr_table} SET [DATA] = {json.dumps(self.data)} WHERE kpi=TRUE AND Month(month)=MONTH(current_date()) AND year(month)=YEAR(current_date()))
                ELSE 
                INSERT INTO {self.okr_table} (month, data, kpi) SELECT current_date(),parse_json('{json.dumps(self.data)}'), true;
                END"""

Lukasz Szozda · Accepted Answer · 2022-07-06T15:30:17.437

To perfrom INSERT/UPDATE it is better to use single MERGE statement

I have looked at merge but it doesn't seem to fit me since my data isn't a table

It is not an issue as source could be a table or subquery:

MERGE INTO {self.okr_table} 
USING (SELECT PARSE_JSON({json.dumps(self.data)} AS data
             , MONTH(current_date()) AS month
             , YEAR(current_date()) AS year
      ) s
  ON  {self.okr_table}.KPI
  AND MONTH({self.okr_table}.month) = s.month
  AND YEAR({self.okr_table}.month) = s.year
WHEN MATCHED THEN UPDATE
WHEN NOT MATCHED THEN INSER ...;

IF/ELSE branching works in Snowflake:

BEGIN
  IF (EXISTS (...)) THEN
      UPDATE ... ;
  ELSE
      INSERT ... ;
  END IF;
END;

Please note ; after each statement, END IF and parenthesis around condition.

Snowfalke sql update if exists else insert

1 Answers1