How to update an existing row in BigQuery

Question

I am just getting started with BQ and its Node.js client, and for my use case, I would like to insert or update rows depending on if they already exist based on a unique identifier that is stored in one of the columns.

I don't see any examples in the node client repository of how to update a row instead of injecting.

So in other words, I want to query my table to see if the "id" field already matches x. If it exists, I want to replace the row, if it doesn't, I want to inject it.

How would I accomplish this?

Answer 1

You can also use a Bigquery merge query with the NodeJs client :

MERGE dataset.Inventory T
USING dataset.NewArrivals S
ON T.product = S.product
WHEN MATCHED THEN
  UPDATE SET quantity = T.quantity + S.quantity
WHEN NOT MATCHED THEN
  INSERT (product, quantity) VALUES(product, quantity)

The merge queries allows updating the element if it exists in Bigquery, or otherwise insert it.

If you are interested I written an article showing merge query and how to remove duplicates in batch pipelines using BigQuery