Data Manipulation Language

The BigQuery Data Manipulation Language (DML) enables you to update, insert, and delete data from your BigQuery tables.

You can execute DML statements just as you would a SELECT statement, with the following conditions:

  • You must use standard SQL. To enable standard SQL, see Enabling Standard SQL.
  • You cannot specify a destination table. For example, in the web UI you must have Destination Table set to No table selected.

Screenshot of BigQuery web UI showing no destination table selected

Quotas

DML statements are significantly more expensive to process than SELECT statements.

  • Maximum UPDATE/DELETE statements per day per table: 48
  • Maximum UPDATE/DELETE statements per day per project: 500
  • Maximum INSERT statements per day per table: 1,000
  • Maximum INSERT statements per day per project: 10,000

For other BigQuery quota information, see Quota Policy.

Pricing

BigQuery charges for DML queries based on the number of bytes processed by the query. The number of bytes processed is calculated as follows.

Action Notes
INSERT

Bytes processed = sum of bytes processed for all the fields referenced from the tables scanned by the query.

UPDATE

Bytes processed = sum of bytes in referenced fields in the scanned tables + the sum of bytes for all fields in the updated table at the time the UPDATE starts.

Example 1:

Table1 has two fields, col1 of type integer and col2 of type string.

UPDATE table1 SET col1 = 1 WHERE col1 = 2;

Bytes processed in example 1:
sum of the number of bytes in col1 + sum of the number of bytes in col2.

Example 2:

Table1 has two fields, col1 of type integer and col2 of type string; and table2 has one field, field1 of type integer.

UPDATE table1 SET col1 = 1 WHERE col1 in (SELECT field1 from table2)

Bytes processed in example 2:
sum of the number of bytes in table1.col1 before UPDATE +
sum of the number of bytes in table1.col2 before UPDATE +
sum of the number of bytes in table2.field1

DELETE Bytes processed = sum of bytes of referenced fields in the scanned tables + sum of bytes for all fields in the modified table at the time the DELETE starts.

For other BigQuery pricing information, see Pricing.

Known issues

  • DML statements cannot be used to modify tables with REQUIRED fields in their schema.
  • Each DML statement initiates an implicit transaction, which means that changes made by the statement are automatically committed at the end of each successful DML statement. There is no support for multi-statement transactions.
  • The following combinations of DML statements are allowed to run concurrently on a table:

    • UPDATE and INSERT
    • DELETE and INSERT
    • INSERT and INSERT

    Otherwise one of the DML statements will be aborted. For example, if two UPDATE statements execute simultaneously against the table then only one of them will succeed.

  • Tables that have been written to recently via BigQuery Streaming (tabledata.insertall) cannot be modified using UPDATE or DELETE statements. To check if the table has a streaming buffer, check the tables.get response for a section named streamingBuffer. If it is absent, the table can be modified using UPDATE or DELETE statements.

  • DML statements that modify partitioned tables are not yet supported.

What's next

  • See DML syntax and samples on the DML syntax page.

Send feedback about...

BigQuery Documentation