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 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.
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
andINSERT
DELETE
andINSERT
INSERT
andINSERT
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.Rows that were written to a table recently via streaming (using the
tabledata.insertall
method) cannot be modified usingUPDATE
,DELETE
, orMERGE
statements. Recent writes are typically those that occur within the last 30 minutes. Note that all other rows in the table remain modifiable by usingUPDATE
,DELETE
, orMERGE
statements.Correlated subqueries within a
when_clause
,search_condition
,merge_update_clause
ormerge_insert_clause
are not supported forMERGE
statements.If there are only
INSERT
clauses (there can be more than one), theMERGE
statement behaves like anINSERT
statement. AnINSERT
-onlyMERGE
statement can run concurrently with other DML statements (INSERT
,UPDATE
,DELETE
, orMERGE
).If the
MERGE
statement is not limited toINSERT
clauses (it has one or moreUPDATE
orDELETE
clauses), the statement cannot run concurrently with otherMERGE
statements containingUPDATE
orDELETE
clauses. The statement also cannot run concurrently with any otherDELETE
orUPDATE
statements.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
FROM
clause of anUPDATE
query, but a wildcard table cannot be used as the target of theUPDATE
operation.
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.
What's next
- See DML syntax and samples on the DML syntax page.