BigQuery DML Merge Sample

createTable.sql

CREATE OR REPLACE TABLE `<project id>`.`<dataset id>`.`test` 
OPTIONS() AS( SELECT 'a' AS id, '1' AS value );

merge.sql

MERGE INTO
  `<project id>`.`<dataset id>`.`test` AS destination_table
USING
  (
    WITH tmp2 AS (
      SELECT 'a' AS id, '2' AS value
      UNION ALL
      SELECT 'b' AS id, '3' AS value 
    ) SELECT * FROM tmp2 
) AS source_table
ON destination_table.id = source_table.id
WHEN MATCHED THEN UPDATE SET id = source_table.id, value = source_table.value
WHEN NOT MATCHED THEN INSERT
(id, value) VALUES (id, value)