INSERT, UPDATE and DELETE in BigQuery

BigQuery DML needs a WHERE clause on every UPDATE and DELETE, rejects an UPDATE that matches a row twice, bills a filtered UPDATE or DELETE for the whole table, and runs at most two mutating statements per table at once.

BigQuery supports INSERT, UPDATE, DELETE, MERGE and TRUNCATE TABLE, with the same syntax as other databases. The differences are in the rules around them. Every UPDATE and DELETE needs a WHERE. A filtered UPDATE or DELETE is billed for every column of the table (or of the partitions it touches). Only two UPDATE, DELETE or MERGE statements run on a table at a time. BigQuery is built for large batch changes, not for many small ones.

The examples below are scripts: they create a temporary table, change it, and read it back.

UPDATE and DELETE need WHERE

An UPDATE without a WHERE clause is an error, not a change to every row:

CREATE TEMP TABLE accounts (id INT64, owner STRING, plan STRING, balance NUMERIC);
INSERT INTO accounts VALUES
  (1, 'ana', 'free', 0),
  (2, 'ben', 'pro', 120),
  (3, 'cai', 'pro', 45);
UPDATE accounts SET plan = 'free';
SELECT * FROM accounts;

To change every row on purpose, write WHERE TRUE. The same applies to DELETE. With a condition, both behave as usual:

CREATE TEMP TABLE accounts (id INT64, owner STRING, plan STRING, balance NUMERIC);
INSERT INTO accounts VALUES
  (1, 'ana', 'free', 0),
  (2, 'ben', 'pro', 120),
  (3, 'cai', 'pro', 45);
UPDATE accounts SET plan = 'team' WHERE plan = 'pro';
SELECT @@row_count AS rows_updated, (SELECT COUNT(*) FROM accounts WHERE plan = 'team') AS team_accounts;

In a script, @@row_count holds the number of rows the last statement changed.

CREATE TEMP TABLE accounts (id INT64, owner STRING, plan STRING, balance NUMERIC);
INSERT INTO accounts VALUES
  (1, 'ana', 'free', 0),
  (2, 'ben', 'pro', 120),
  (3, 'cai', 'pro', 45);
DELETE FROM accounts WHERE balance = 0;
SELECT id, owner FROM accounts ORDER BY id;

INSERT

Columns you leave out of the column list get their default value, which is NULL unless the column has a DEFAULT:

CREATE TEMP TABLE accounts (id INT64, owner STRING, plan STRING, balance NUMERIC);
INSERT INTO accounts VALUES
  (1, 'ana', 'free', 0),
  (2, 'ben', 'pro', 120),
  (3, 'cai', 'pro', 45);
INSERT INTO accounts (id, owner) VALUES (4, 'dee');
SELECT id, owner, plan, balance FROM accounts WHERE id = 4;

INSERT ... SELECT copies the result of a query:

CREATE TEMP TABLE accounts (id INT64, owner STRING, plan STRING, balance NUMERIC);
INSERT INTO accounts VALUES
  (1, 'ana', 'free', 0),
  (2, 'ben', 'pro', 120),
  (3, 'cai', 'pro', 45);
CREATE TEMP TABLE pro_accounts (id INT64, owner STRING);
INSERT INTO pro_accounts
SELECT id, owner FROM accounts WHERE plan = 'pro';
SELECT * FROM pro_accounts ORDER BY id;

For more than a handful of rows at a time, load files or stream instead of running many small INSERT ... VALUES statements. See the limits below and Loading data.

UPDATE from another table

UPDATE ... FROM joins the target with another table and updates the rows that match:

CREATE TEMP TABLE accounts (id INT64, owner STRING, plan STRING, balance NUMERIC);
INSERT INTO accounts VALUES
  (1, 'ana', 'free', 0),
  (2, 'ben', 'pro', 120),
  (3, 'cai', 'pro', 45);
CREATE TEMP TABLE payments (account_id INT64, amount NUMERIC);
INSERT INTO payments VALUES (2, 30), (3, 5);
UPDATE accounts AS a
SET balance = a.balance + p.amount
FROM payments AS p
WHERE p.account_id = a.id;
SELECT id, balance FROM accounts ORDER BY id;

If a target row matches more than one source row, BigQuery doesn’t pick one or apply both. The whole statement fails:

CREATE TEMP TABLE accounts (id INT64, owner STRING, plan STRING, balance NUMERIC);
INSERT INTO accounts VALUES
  (1, 'ana', 'free', 0),
  (2, 'ben', 'pro', 120),
  (3, 'cai', 'pro', 45);
CREATE TEMP TABLE payments (account_id INT64, amount NUMERIC);
INSERT INTO payments VALUES (2, 30), (2, 10);
UPDATE accounts AS a
SET balance = a.balance + p.amount
FROM payments AS p
WHERE p.account_id = a.id;
SELECT id, balance FROM accounts;

Aggregate the source first so that each target row matches at most one source row:

CREATE TEMP TABLE accounts (id INT64, owner STRING, plan STRING, balance NUMERIC);
INSERT INTO accounts VALUES
  (1, 'ana', 'free', 0),
  (2, 'ben', 'pro', 120),
  (3, 'cai', 'pro', 45);
CREATE TEMP TABLE payments (account_id INT64, amount NUMERIC);
INSERT INTO payments VALUES (2, 30), (2, 10);
UPDATE accounts AS a
SET balance = a.balance + p.total
FROM (SELECT account_id, SUM(amount) AS total FROM payments GROUP BY account_id) AS p
WHERE p.account_id = a.id;
SELECT id, balance FROM accounts ORDER BY id;

The same rule applies to MERGE. See MERGE.

Updating a field inside a struct

SET can target one field of a STRUCT column, leaving the other fields alone:

CREATE TEMP TABLE customers (id INT64, address STRUCT<city STRING, zip STRING>);
INSERT INTO customers VALUES (1, STRUCT('Paris', '75001'));
UPDATE customers SET address.city = 'Lyon' WHERE id = 1;
SELECT id, address.city, address.zip FROM customers;

What DML costs

DML statements are billed like queries, but what they read is surprising. We dry-ran each statement on a 20,000-row table partitioned by day into 10 partitions, with columns day DATE, id INT64 and note STRING (2.25 MB in total, of which id is 156 KB):

What follows from it:

• An UPDATE or DELETE with a row filter reads every column of every partition it can’t rule out, because BigQuery rewrites whole blocks of the table. Filtering on one small column doesn’t make it cheap.
• A filter on the partitioning column limits that to the matching partitions. Always add one when you can.
• Deleting whole partitions, emptying a table, or inserting literal rows reads nothing. When a filter is only on the partitioning column, BigQuery drops the partitions without reading them.

On a table with 10 TB of data, a one-row UPDATE without a partition filter is billed as 10 TB. See How a query is billed and Partitioning.

Concurrency and limits

These are per table:

When two mutating statements change the same partition at the same time, one of them fails with a conflict. BigQuery retries it up to three times. An INSERT never conflicts, and a MERGE that only inserts doesn’t either.

So BigQuery works well for a nightly job that rewrites millions of rows in one statement, and badly for an application that updates single rows thousands of times a day. Batch changes into a staging table and apply them with one MERGE.

Rows that were just streamed

Rows written in the last 30 minutes through the Storage Write API’s REST interface can’t be changed by UPDATE, DELETE, MERGE or TRUNCATE yet. Older rows in the same table can. Rows written through the gRPC interface can be changed right away, except inside a multi-statement transaction. See Streaming.

Fine-grained DML

By default BigQuery rewrites whole groups of files to change a few rows, which costs slot time on large tables with sparse changes. Setting the table option enable_fine_grained_mutations = TRUE makes it rewrite less data and clean up deleted rows in the background. It reduces slot use and run time. It doesn’t reduce the bytes billed for the statement.

ALTER TABLE mydataset.accounts
SET OPTIONS (enable_fine_grained_mutations = TRUE);

Transactions

Each DML statement is atomic on its own. To change several tables together, wrap the statements in BEGIN TRANSACTION … COMMIT TRANSACTION in a script. See Transactions.