How to do an UPSERT in Google BigQuery?

There are several ways to implement an UPSERT operation in BigQuery. I will describe one of these approaches in this article.

To begin with, let’s imagine that we have a table with two fields, id and name:

We want to perform an UPDATE of a record by the id key, and if a value with that key is not found, then perform an INSERT.

To do this, I suggest using the MERGE operator combined with the id key, which will be used to match records.

An example query that implements the UPSERT operation might look like this:

MERGE `mydataset.mytable` t
USING (
  SELECT 1 AS id, "John" AS name UNION ALL
  SELECT 2 AS id, "Jane" AS name UNION ALL
  SELECT 3 AS id, "Bob" AS name
) s
ON t.id = s.id
WHEN MATCHED THEN
  UPDATE SET name = s.name
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (s.id, s.name)

This query merges the table **mydataset.mytable** with a temporary table **s** that contains the new records to insert or update.

The MERGE operator matches records by the id field, and if a record with that id already exists in the table **mydataset.mytable**, then its name field is updated with the values from the temporary table **s**. If, on the other hand, a record with that id is absent from the table **mydataset.mytable**, then it is inserted together with the corresponding values of the id and name fields from the temporary table **s**.

As a result of running the query above, we get:

Thus, the MERGE operator makes it possible to efficiently implement the UPSERT operation in BigQuery. However, it is worth remembering that this operator can have a high execution cost, especially for large tables, and you need to verify how it performs in practice.