How to update an existing row in BigQuery-CodePudding

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?

CodePudding user response：

I found an answer here

So apparently BigQuery is not designed to behave like a database. Instead of updating existing rows, you keep appending data as new rows and later use a query to clean up the table by removing duplicates like described here

CodePudding user response：

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.