# 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.

Source: https://docs.querylab.io/bigquery/dml/

Updated: 2026-10-06

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:

**UPDATE with no WHERE**

```sql
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;
```

BigQuery rejects this query:

```text
Query error: UPDATE must have a WHERE clause at [6:1]
```

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

**UPDATE with WHERE, and @@row_count**

```sql
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;
```

Result in BigQuery (1 row):

| rows_updated | team_accounts |
| --- | --- |
| 2 | 2 |

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

**DELETE the accounts with no balance**

```sql
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;
```

Result in BigQuery (2 rows):

| id | owner |
| --- | --- |
| 2 | ben |
| 3 | cai |

## INSERT

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

**INSERT with a column list**

```sql
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;
```

Result in BigQuery (1 row):

| id | owner | plan | balance |
| --- | --- | --- | --- |
| 4 | dee | NULL | NULL |

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

**INSERT ... SELECT**

```sql
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;
```

Result in BigQuery (2 rows):

| id | owner |
| --- | --- |
| 2 | ben |
| 3 | cai |

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](https://docs.querylab.io/bigquery/loading-data/).

## UPDATE from another table

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

**add each payment to its account**

```sql
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;
```

Result in BigQuery (3 rows):

| id | balance |
| --- | --- |
| 1 | 0 |
| 2 | 150 |
| 3 | 50 |

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

**two payments for the same account**

```sql
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;
```

BigQuery rejects this query:

```text
Query error: UPDATE/MERGE must match at most one source row for each target row at [8:1]
```

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

**sum the payments before updating**

```sql
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;
```

Result in BigQuery (3 rows):

| id | balance |
| --- | --- |
| 1 | 0 |
| 2 | 160 |
| 3 | 45 |

The same rule applies to `MERGE`. See [MERGE](https://docs.querylab.io/bigquery/merge/).

## Updating a field inside a struct

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

**change only address.city**

```sql
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;
```

Result in BigQuery (1 row):

| id | city | zip |
| --- | --- | --- |
| 1 | Lyon | 75001 |

## 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):

| Statement                                                   | Bytes processed                  |
| ----------------------------------------------------------- | -------------------------------- |
| `SELECT id FROM t`                                          | 156 KB                           |
| `UPDATE t SET note = 'y' WHERE id = 5`                      | 2.25 MB, the whole table         |
| `DELETE FROM t WHERE id = 5`                                | 2.25 MB, the whole table         |
| `UPDATE` / `DELETE ... WHERE day = '2026-01-03' AND id = 5` | 230 KB, the whole partition      |
| `DELETE FROM t WHERE day = '2026-01-03'`                    | 0 B                              |
| `DELETE FROM t WHERE TRUE`                                  | 0 B                              |
| `TRUNCATE TABLE t`                                          | 0 B                              |
| `INSERT INTO t VALUES (...)`                                | 0 B                              |
| `INSERT INTO t SELECT * FROM t WHERE id < 3`                | 2.25 MB, what the `SELECT` reads |

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](https://docs.querylab.io/bigquery/how-queries-are-billed/) and [Partitioning](https://docs.querylab.io/bigquery/partitioning/).

## Concurrency and limits

These are per table:

| Limit                                                 | Value                                            |
| ----------------------------------------------------- | ------------------------------------------------ |
| `UPDATE`, `DELETE`, `MERGE` running at once           | 2; more are queued                               |
| Mutating statements waiting in the queue              | 20; more fail                                    |
| Time a statement may wait in the queue                | 7 hours                                          |
| `INSERT` statements per day that run without queueing | 1,500; after that 10 at a time, up to 100 queued |
| DML statements of any kind                            | 25 every 10 seconds                              |
| DML statements per day                                | unlimited                                        |

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](https://docs.querylab.io/bigquery/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.

```sql
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](https://docs.querylab.io/bigquery/transactions/).

Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Transform data with data manipulation language (DML)](https://cloud.google.com/bigquery/docs/data-manipulation-language)

- [DML syntax](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax)

- [Quotas and limits, DML statements](https://cloud.google.com/bigquery/quotas#data-manipulation-language-statements)