Data manipulation language (DML) statements in GoogleSQL
The BigQuery data manipulation language (DML) enables you to update, insert, and delete data from your BigQuery tables.
For information about how to use DML statements, see Using data manipulation language.
On-demand query size calculation
If you use on-demand billing, BigQuery charges for data manipulation language (DML) statements based on the number of bytes processed by the statement.
For more information about cost estimation, see Estimate and control costs.
Non-partitioned tables
For non-partitioned tables, the number of bytes processed is calculated as follows:
- q = The sum of bytes processed by the DML statement itself, including any columns referenced in tables scanned by the DML statement.
- t = The sum of bytes for all columns in the table being updated by the DML statement, as they are at the time the query starts. All columns are included, regardless of whether those columns are referenced in or modified by the DML statement.
DML statement | Bytes processed |
---|---|
INSERT |
q |
UPDATE |
q + t |
DELETE |
q + t |
MERGE |
If there are only INSERT clauses: q. If there is an UPDATE or DELETE clause: q + t. |
Partitioned tables
For partitioned tables, the number of bytes processed is calculated as follows:
- q' = The sum of bytes processed by the DML statement itself, including any columns referenced in all partitions scanned by the DML statement.
- t' = The sum of bytes for all columns in the partitions being updated by the DML statement, as they are at the time the query starts. All columns are included, regardless of whether those columns are referenced in or modified by the DML statement.
DML statement | Bytes processed |
---|---|
INSERT |
q' |
UPDATE |
q' + t' |
DELETE |
q' + t' |
MERGE |
If there are only INSERT clauses in the MERGE statement: q'. If there is an UPDATE or DELETE clause in the MERGE statement: q' + t'. |
INSERT
statement
Use the INSERT
statement when you want to add new rows to a table.
INSERT [INTO] target_name
[(column_1 [, ..., column_n ] )]
input
input ::=
VALUES (expr_1 [, ..., expr_n ] )
[, ..., (expr_k_1 [, ..., expr_k_n ] ) ]
| SELECT_QUERY
expr ::= value_expression | DEFAULT
INSERT
statements must comply with the following rules:
- Column names are optional if the target table is not an ingestion-time partitioned table.
- Duplicate names are not allowed in the list of target columns.
- Values must be added in the same order as the specified columns.
- The number of values added must match the number of specified columns.
- Values must have a type that is compatible with the target column.
- When the value expression is
DEFAULT
, the default value for the column is used. If the column has no default value, the value defaults toNULL
.
Omitting column names
When the column names are omitted, all columns in the target table are included
in ascending order based on their ordinal positions. If an omitted column has
a default value, then that value is used. Otherwise, the column value is NULL
.
If the target
table is an
ingestion-time partitioned table,
column names must be specified.
Value type compatibility
Values added with an INSERT
statement must be compatible with the target
column's type. A value's type is considered compatible with the target column's
type if one of the following criteria are met:
- The value type matches the column type exactly. For example, inserting a value of type INT64 in a column that also has a type of INT64.
- The value type is one that can be implicitly coerced into another type.
INSERT
examples
INSERT
using explicit values
INSERT dataset.Inventory (product, quantity)
VALUES('top load washer', 10),
('front load washer', 20),
('dryer', 30),
('refrigerator', 10),
('microwave', 20),
('dishwasher', 30),
('oven', 5)
+-------------------+----------+--------------------+ | product | quantity | supply_constrained | +-------------------+----------+--------------------+ | dishwasher | 30 | NULL | | dryer | 30 | NULL | | front load washer | 20 | NULL | | microwave | 20 | NULL | | oven | 5 | NULL | | refrigerator | 10 | NULL | | top load washer | 10 | NULL | +-------------------+----------+--------------------+
If you set a default value for a column, then you can use the DEFAULT
keyword
in place of a value to insert the default value:
ALTER TABLE dataset.NewArrivals ALTER COLUMN quantity SET DEFAULT 100;
INSERT dataset.NewArrivals (product, quantity, warehouse)
VALUES('top load washer', DEFAULT, 'warehouse #1'),
('dryer', 200, 'warehouse #2'),
('oven', 300, 'warehouse #3');
+-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 200 | warehouse #2 | | oven | 300 | warehouse #3 | | top load washer | 100 | warehouse #1 | +-----------------+----------+--------------+
INSERT SELECT
statement
INSERT dataset.Warehouse (warehouse, state)
SELECT *
FROM UNNEST([('warehouse #1', 'WA'),
('warehouse #2', 'CA'),
('warehouse #3', 'WA')])
+--------------+-------+ | warehouse | state | +--------------+-------+ | warehouse #1 | WA | | warehouse #2 | CA | | warehouse #3 | WA | +--------------+-------+
You can also use WITH
when using INSERT SELECT
. For example, you can
rewrite the previous query using WITH
:
INSERT dataset.Warehouse (warehouse, state)
WITH w AS (
SELECT ARRAY<STRUCT<warehouse string, state string>>
[('warehouse #1', 'WA'),
('warehouse #2', 'CA'),
('warehouse #3', 'WA')] col
)
SELECT warehouse, state FROM w, UNNEST(w.col)
The following example shows how to copy a table's contents into another table:
INSERT dataset.DetailedInventory (product, quantity, supply_constrained)
SELECT product, quantity, false
FROM dataset.Inventory
+----------------------+----------+--------------------+----------+----------------+ | product | quantity | supply_constrained | comments | specifications | +----------------------+----------+--------------------+----------+----------------+ | dishwasher | 30 | false | [] | NULL | | dryer | 30 | false | [] | NULL | | front load washer | 20 | false | [] | NULL | | microwave | 20 | false | [] | NULL | | oven | 5 | false | [] | NULL | | refrigerator | 10 | false | [] | NULL | | top load washer | 10 | false | [] | NULL | +----------------------+----------+--------------------+----------+----------------+
INSERT VALUES
with subquery
The following example shows how to insert a row into a table, where one of the values is computed using a subquery:
INSERT dataset.DetailedInventory (product, quantity)
VALUES('countertop microwave',
(SELECT quantity FROM dataset.DetailedInventory
WHERE product = 'microwave'))
+----------------------+----------+--------------------+----------+----------------+ | product | quantity | supply_constrained | comments | specifications | +----------------------+----------+--------------------+----------+----------------+ | countertop microwave | 20 | NULL | [] | NULL | | dishwasher | 30 | false | [] | NULL | | dryer | 30 | false | [] | NULL | | front load washer | 20 | false | [] | NULL | | microwave | 20 | false | [] | NULL | | oven | 5 | false | [] | NULL | | refrigerator | 10 | false | [] | NULL | | top load washer | 10 | false | [] | NULL | +----------------------+----------+--------------------+----------+----------------+
INSERT
without column names
INSERT dataset.Warehouse VALUES('warehouse #4', 'WA'), ('warehouse #5', 'NY')
This is the Warehouse
table before you run the query:
+--------------+-------+ | warehouse | state | +--------------+-------+ | warehouse #1 | WA | | warehouse #2 | CA | | warehouse #3 | WA | +--------------+-------+
This is the Warehouse
table after you run the query:
+--------------+-------+ | warehouse | state | +--------------+-------+ | warehouse #1 | WA | | warehouse #2 | CA | | warehouse #3 | WA | | warehouse #4 | WA | | warehouse #5 | NY | +--------------+-------+
INSERT
with STRUCT
types
The following example shows how to insert a row into a table, where some of
the fields are
STRUCT
types.
INSERT dataset.DetailedInventory
VALUES('top load washer', 10, FALSE, [(CURRENT_DATE, "comment1")], ("white","1 year",(30,40,28))),
('front load washer', 20, FALSE, [(CURRENT_DATE, "comment1")], ("beige","1 year",(35,45,30)))
Here is the DetailedInventory
table after you run the query:
+-------------------+----------+--------------------+-------------------------------------------------+----------------------------------------------------------------------------------------------------+ | product | quantity | supply_constrained | comments | specifications | +-------------------+----------+--------------------+-------------------------------------------------+----------------------------------------------------------------------------------------------------+ | front load washer | 20 | false | [{"created":"2021-02-09","comment":"comment1"}] | {"color":"beige","warranty":"1 year","dimensions":{"depth":"35.0","height":"45.0","width":"30.0"}} | | top load washer | 10 | false | [{"created":"2021-02-09","comment":"comment1"}] | {"color":"white","warranty":"1 year","dimensions":{"depth":"30.0","height":"40.0","width":"28.0"}} | +-------------------+----------+--------------------+-------------------------------------------------+----------------------------------------------------------------------------------------------------+
INSERT
with ARRAY
types
The following example show how to insert a row into a table, where one of the
fields is an ARRAY
type.
CREATE TABLE IF NOT EXISTS dataset.table1 (names ARRAY<STRING>);
INSERT INTO dataset.table1 (names)
VALUES (["name1","name2"])
Here is the table after you run the query:
+-------------------+
| names |
+-------------------+
| ["name1","name2"] |
+-------------------+
DELETE
statement
Use the DELETE
statement when you want to delete rows from a table.
DELETE [FROM] target_name [alias]
WHERE condition
To delete all rows in a table, use the TRUNCATE TABLE statement.
WHERE
keyword
Each time you construct a DELETE
statement, you must use the WHERE
keyword,
followed by a condition.
The WHERE
keyword is mandatory for any DELETE
statement.
DELETE
examples
DELETE
with WHERE
clause
DELETE dataset.Inventory
WHERE quantity = 0
Before:
+-------------------+----------+--------------------+ | product | quantity | supply_constrained | +-------------------+----------+--------------------+ | dishwasher | 20 | NULL | | dryer | 30 | NULL | | front load washer | 10 | NULL | | microwave | 20 | NULL | | oven | 5 | NULL | | refrigerator | 10 | NULL | | top load washer | 0 | NULL | +-------------------+----------+--------------------+
After:
+-------------------+----------+--------------------+ | product | quantity | supply_constrained | +-------------------+----------+--------------------+ | dishwasher | 20 | NULL | | dryer | 30 | NULL | | front load washer | 10 | NULL | | microwave | 20 | NULL | | oven | 5 | NULL | | refrigerator | 10 | NULL | +-------------------+----------+--------------------+
DELETE
with subquery
DELETE dataset.Inventory i
WHERE i.product NOT IN (SELECT product from dataset.NewArrivals)
Before:
Inventory +-------------------+----------+--------------------+ | product | quantity | supply_constrained | +-------------------+----------+--------------------+ | dishwasher | 30 | NULL | | dryer | 30 | NULL | | front load washer | 20 | NULL | | microwave | 20 | NULL | | oven | 5 | NULL | | refrigerator | 10 | NULL | | top load washer | 10 | NULL | +-------------------+----------+--------------------+ NewArrivals +-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 200 | warehouse #2 | | oven | 300 | warehouse #3 | | top load washer | 100 | warehouse #1 | +-----------------+----------+--------------+
After:
Inventory +-----------------+----------+--------------------+ | product | quantity | supply_constrained | +-----------------+----------+--------------------+ | dryer | 30 | NULL | | oven | 5 | NULL | | top load washer | 10 | NULL | +-----------------+----------+--------------------+
Alternately, you can use DELETE
with the EXISTS
clause:
DELETE dataset.Inventory
WHERE NOT EXISTS
(SELECT * from dataset.NewArrivals
WHERE Inventory.product = NewArrivals.product)
TRUNCATE TABLE
statement
The TRUNCATE TABLE
statement removes all rows from a table but leaves the
table metadata intact, including the table schema, description, and labels.
TRUNCATE TABLE [[project_name.]dataset_name.]table_name
Where:
project_name
is the name of the project containing the table. Defaults to the project that runs this DDL query.dataset_name
is the name of the dataset containing the table.table_name
is the name of the table to truncate.
Truncating views, materialized views, models, or external tables is not
supported. Quotas and limits for queries apply to TRUNCATE TABLE
statements.
For more information, see Quotas and limits.
For partitioned tables, if the table requires a partition filter, then the
TRUNCATE TABLE
statement fails. To truncate the table, first update the table to
remove the partition filter requirement, and then run the TRUNCATE TABLE
statement. For more information, see
Updating partition filter requirements.
TRUNCATE TABLE
examples
The following example removes all rows from the table named Inventory
.