Merge

merge_example.sql

MERGE INTO reporting.transaction_model A 
USING (
    SELECT transaction_id, transaction_date, price, store_name, last_updated
    FROM 
    EXTERNAL_QUERY
        (
        "projects/datapath/locations/us/connections/databaseconnection", 
        "SELECT CAST(transaction_id AS VARCHAR(100)), store_name, transaction_date, price, last_updated FROM public.transaction WHERE transaction_date BETWEEN NOW() - INTERVAL '2 DAY' AND NOW();"
        )
    ) B
ON A.transaction_id = CAST(B.transaction_id as STRING)
AND A.transaction_date BETWEEN TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -2 DAY) AND CURRENT_TIMESTAMP()
WHEN MATCHED THEN UPDATE SET 
  A.price = B.price,
  A.store_name = B.store_name,
  A.last_updated = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN INSERT (transaction_id, store_name, transaction_date, price, last_updated)
VALUES (cast(transaction_id as STRING), store_name, CAST(transaction_date AS TIMESTAMP), price, CAST(last_updated AS TIMESTAMP));
END;