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

For information on DML quota information, see DML statements on the Quotas and Limits page.

Pricing

For information on DML pricing, see Data Manipulation Language pricing on the Pricing page.

Limitations

  • 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.
  • Only 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 streaming (tabledata.insertall) cannot be modified using UPDATE, DELETE, or MERGE statements. To check if the table has a streaming buffer, examine the tables.get response for a section named streamingBuffer. If it is absent, the table can be modified using UPDATE, DELETE, or MERGE statements.

  • Correlated subqueries within a when_clause, search_condition, merge_update_clause or merge_insert_clause are not supported for MERGE statements.
  • If there are only INSERT clauses (there can be more than one), the MERGE statement behaves like an INSERT statement. An INSERT-only MERGE statement can run concurrently with other DML statements (INSERT, UPDATE, DELETE, or MERGE).

    If the MERGE statement is not limited to INSERT clauses (it has one or more UPDATE or DELETE clauses), the statement cannot run concurrently with other MERGE statements containing UPDATE or DELETE clauses. The statement also cannot run concurrently with any other DELETE or UPDATE statements.

What's next

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

Send feedback about...