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 to NULL.

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.

Data manipulation language (DML) statements in GoogleSQL

On-demand query size calculation

Non-partitioned tables

Partitioned tables

INSERT statement

Omitting column names

Value type compatibility

INSERT examples

INSERT using explicit values

INSERT SELECT statement

INSERT VALUES with subquery

INSERT without column names

INSERT with STRUCT types

INSERT with ARRAY types

DELETE statement

WHERE keyword

DELETE examples

DELETE with WHERE clause

DELETE with subquery

TRUNCATE TABLE statement

TRUNCATE TABLE examples