Converting an on conflict clause to snowflake-CodePudding

How to convert the following Postgres query into Snowflake -

INSERT INTO infer (claim_id, patient_id, 

rend, bill, from_date, to_date, diff, dx)
SELECT claim_id, patient_id, rend, bill, from_date, to_date, diff, dx
FROM infer_2021q1
ON CONFLICT (claim_id, rend, bill, from_date, to_date, dx)
DO UPDATE 
SET 
patient_id = excluded.patient_id,
to_rend = excluded.to_rend,
to_bill = excluded.to_bill,
diff = excluded.diff

In Postgres, I am able to run this because columns in the on conflict clause are a unique index. But Snowflake doesn't have indexes so not sure how to run such a query within reasonable time and resource utilization.

CodePudding user response：

Snowflake supports MERGE statement:

Inserts, updates, and deletes values in a table based on values in a second table or a subquery. This can be useful if the second table is a change log that contains new rows (to be inserted), modified rows (to be updated), and/or marked rows (to be deleted) in the target table.

MERGE INTO infer 
USING infer_2021q1
  ON infer.claim_id = infer_2021q1.rend
AND infer.bill = infer_2021q1.bill 
AND infer.from_date =infer_2021q1.from_date 
AND infer.to_date = infer_2021q1.to_date 
AND infer.dx = infer_2021q1.dx 
WHEN MATCHED THEN 
  SET patient_id = infer_2021q1.patient_id,
     to_rend = infer_2021q1.to_rend,
     to_bill = infer_2021q1.to_bill,
     diff = infer_2021q1.diff
WHEN NOT MATCHED THEN
  INSERT infer (claim_id, patient_id, rend, bill, from_date, to_date, diff, dx)
  VALUES (infer_2021q1.claim_id, infer_2021q1.patient_id, infer_2021q1.rend,
         infer_2021q1.bill, infer_2021q1.from_date, infer_2021q1.to_date,
         infer_2021q1.diff, infer_2021q1.dx);