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
- You must use standard SQL. To enable standard SQL, see Switching SQL dialects.
You cannot specify a destination table. For example, in the web UI you must have Destination Table set to No table selected.
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.
Rows that were written to a table recently via streaming (using the
tabledata.insertallmethod) cannot be modified using
MERGEstatements. Recent writes are typically those that occur within the last 30 minutes. Note that all other rows in the table remain modifiable by using
Correlated subqueries within a
merge_insert_clauseare not supported for
Queries that contain Data Manipulation Language (DML) statements cannot use a wildcard table as the target of the query. For example, a wildcard table may be used in the
FROMclause of an
UPDATEquery, but a wildcard table cannot be used as the target of the
BigQuery manages the concurrency of DML statements that add or modify or delete rows in a table.
INSERT DML concurrency
During any 24 hour period, the first 1000 statements that
INSERT into a table
will run concurrently. After this limit is reached, the concurrency of
statements that write to a table is limited to 10. Any
INSERT DML jobs beyond
10 are queued in PENDING state. After a previous job
finishes, the next PENDING job is dequeued and run. Currently, up to
INSERT DML statements can be queued against a table at any given
UPDATE, DELETE, MERGE DML concurrency
We refer to
MERGE DML statements as mutating DML statements.
When you submit more than one mutating DML statement simultaneously to a table,
BigQuery runs a fixed number of these statements concurrently. Any
additional mutating DML jobs against that table are automatically queued in
PENDING state. When a previously running job finishes, the next PENDING job is
dequeued and run.
INTERACTIVE priority DML jobs that are queued for more than 6 hours will fail.
DML statement conflicts
Concurrently running mutating DML statements on a table might fail due to conflicts in the changes they make. BigQuery retries these failures.
INSERTDML statement that inserts rows to a table will not conflict with any other concurrently running DML statement.
MERGEDML statements that contain only an
INSERTclause with no
DELETEclauses will not conflict with any other concurrently running DML statement.
MERGEDML statements with
DELETEclauses will not conflict with any other concurrently running DML statement as long the
MERGEstatement does not lead to update or deletion of existing rows.
For information on DML pricing, see Data Manipulation Language pricing on the Pricing page.
- See DML syntax and samples on the DML syntax page.