Data Manipulation Language Syntax

The BigQuery Dynamic Manipulation Language (DML) enables you to update, insert, and delete data from your BigQuery tables.

For information about how to use DML statements, see Data Manipulation Language.

Tables used in examples

Inventory

[
  {"name": "product", "type": "string"},
  {"name": "quantity", "type": "integer"},
  {"name": "supply_constrained", "type": "boolean"}
]

NewArrivals

[
  {"name": "product", "type": "string"},
  {"name": "quantity", "type": "integer"},
  {"name": "warehouse", "type": "string"}
]

Warehouse

[
  {"name": "warehouse", "type": "string"},
  {"name": "state", "type": "string"}
]

DetailedInventory

[
  {"name": "product", "type": "string"},
  {"name": "quantity", "type": "integer"},
  {"name": "supply_constrained", "type": "boolean"},
  {"name": "comments", "type": "record", "mode": "repeated", "fields": [
    {"name": "created", "type": "date"},
    {"name": "comment", "type": "string"}
  ]},
  {"name": "specifications", "type": "record", "fields": [
    {"name": "color", "type": "string"},
    {"name": "warranty", "type": "string"},
    {"name": "dimensions", "type": "record", "fields": [
      {"name": "depth", "type": "float"},
      {"name": "height", "type": "float"},
      {"name": "width", "type": "float"}
    ]}
  ]}
]

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

INSERT statements must comply with the following rules:

  • Column names must be specified.
  • 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.

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 |
+-------------------+----------+--------------------+
INSERT dataset.NewArrivals (product, quantity, warehouse)
VALUES('top load washer', 100, '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 |
+----------------------+----------+--------------------+----------+----------------+

DELETE statement

Use the DELETE statement when you want to delete rows from a table.

DELETE [FROM] target_name [alias]
WHERE condition

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.

To delete all rows in a table, set the WHERE keyword condition to true:

DELETE FROM dataset.DetailedInventory WHERE true

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)

UPDATE statement

Use the UPDATE statement when you want to update existing rows within a table.

UPDATE target_name [alias]
SET update_item [, update_item]*
[FROM from_clause]
WHERE condition

update_item ::= path_expression = expression

Where:

  • target_name is the name of a table to update
  • update_item comprises a column name and an update expression

WHERE keyword

Each UPDATE statement must include the WHERE keyword, followed by a condition.

To update all rows in the table, use WHERE true.

FROM keyword

You can use FROM to specify the rows to update. You can also use columns from joined tables in SET expressions.

If a row in the table to be updated joins with zero rows from the FROM clause, then the row isn't updated.

If a row in the table to be updated joins with exactly one row from the FROM clause, then the row is updated.

If a row in the table to be updated joins with more than one row from FROM clause, then the query generates a runtime error.

To specify the join predicate between the table to be updated and tables in the FROM clause, use the WHERE clause. For an example, see UPDATE using joins.

UPDATE examples

UPDATE with WHERE clause

The following example updates a table named Inventory by reducing the value of the quantity field by 10 for all products that contain the string washer:

UPDATE dataset.Inventory
SET quantity = quantity - 10
WHERE product like '%washer%'

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 |
+-------------------+----------+--------------------+

After:

Inventory
+-------------------+----------+--------------------+
|      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 |
+-------------------+----------+--------------------+

UPDATE using joins

The following example:

  • Generates a table with inventory totals that include existing inventory and inventory from the NewArrivals table, and
  • Marks supply_constrained as false:
UPDATE dataset.Inventory
SET quantity = quantity +
  (SELECT quantity FROM dataset.NewArrivals
   WHERE Inventory.product = NewArrivals.product),
    supply_constrained = false
WHERE product IN (SELECT product FROM dataset.NewArrivals)

Alternately, you can join the tables:

UPDATE dataset.Inventory i
SET quantity = i.quantity + n.quantity,
    supply_constrained = false
FROM dataset.NewArrivals n
WHERE i.product = n.product

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:

+-------------------+----------+--------------------+
|      product      | quantity | supply_constrained |
+-------------------+----------+--------------------+
| dishwasher        |       30 |               NULL |
| dryer             |      230 |              false |
| front load washer |       20 |               NULL |
| microwave         |       20 |               NULL |
| oven              |      305 |              false |
| refrigerator      |       10 |               NULL |
| top load washer   |      110 |              false |
+-------------------+----------+--------------------+

UPDATE nested fields

The following example updates nested record fields.

UPDATE dataset.DetailedInventory
SET specifications.color = 'white',
    specifications.warranty = '1 year'
WHERE product like '%washer%'

Alternatively, you can update the entire record:

UPDATE dataset.DetailedInventory
SET specifications
   = STRUCT<color STRING, warranty STRING,
   dimensions STRUCT<depth FLOAT64, height FLOAT64, width FLOAT64>>('white', '1 year', NULL)
WHERE product like '%washer%'
+----------------------+----------+--------------------+----------+---------------------------------------------------------+
|       product        | quantity | supply_constrained | comments |                     specifications                      |
+----------------------+----------+--------------------+----------+---------------------------------------------------------+
| countertop microwave |       20 |               NULL |       [] |                                                    NULL |
| dishwasher           |       30 |              false |       [] | {"color":"white","warranty":"1 year","dimensions":null} |
| dryer                |       30 |              false |       [] |                                                    NULL |
| front load washer    |       20 |              false |       [] | {"color":"white","warranty":"1 year","dimensions":null} |
| microwave            |       20 |              false |       [] |                                                    NULL |
| oven                 |        5 |              false |       [] |                                                    NULL |
| refrigerator         |       10 |              false |       [] |                                                    NULL |
| top load washer      |       10 |              false |       [] | {"color":"white","warranty":"1 year","dimensions":null} |
+----------------------+----------+--------------------+----------+---------------------------------------------------------+

UPDATE repeated records

The following example appends an entry to a repeated record in the comments column for products that contain the string washer:

UPDATE dataset.DetailedInventory
SET comments = ARRAY(
  SELECT comment FROM UNNEST(comments) AS comment
  UNION ALL
  SELECT (CAST('2016-01-01' AS DATE), 'comment1')
)
WHERE product like '%washer%'
+----------------------+----------+--------------------+----------------------------------------------------+----------------+
|       product        | quantity | supply_constrained |                      comments                      | specifications |
+----------------------+----------+--------------------+----------------------------------------------------+----------------+
| countertop microwave |       20 |               NULL |                                                 [] |           NULL |
| dishwasher           |       30 |              false | [u'{"created":"2016-01-01","comment":"comment1"}'] |           NULL |
| dryer                |       30 |              false |                                                 [] |           NULL |
| front load washer    |       20 |              false | [u'{"created":"2016-01-01","comment":"comment1"}'] |           NULL |
| microwave            |       20 |              false |                                                 [] |           NULL |
| oven                 |        5 |              false |                                                 [] |           NULL |
| refrigerator         |       10 |              false |                                                 [] |           NULL |
| top load washer      |       10 |              false | [u'{"created":"2016-01-01","comment":"comment1"}'] |           NULL |
+----------------------+----------+--------------------+----------------------------------------------------+----------------+

Alternatively, you can use the ARRAY_CONCAT function:

UPDATE dataset.DetailedInventory
SET comments = ARRAY_CONCAT(comments,
  ARRAY<STRUCT<created DATE, comment STRING>>[(CAST('2016-01-01' AS DATE), 'comment1')])
WHERE product like '%washer%'

The following example appends a second entry to the repeated record in the comments column for all rows:

UPDATE dataset.DetailedInventory
SET comments = ARRAY(
  SELECT comment FROM UNNEST(comments) AS comment
  UNION ALL
  SELECT (CAST('2016-01-01' AS DATE), 'comment2')
)
WHERE true

SELECT product, comments FROM dataset.DetailedInventory
+----------------------+------------------------------------------------------------------------------------------------------+
|       product        |                                               comments                                               |
+----------------------+------------------------------------------------------------------------------------------------------+
| countertop microwave |                                                   [u'{"created":"2016-01-01","comment":"comment2"}'] |
| dishwasher           | [u'{"created":"2016-01-01","comment":"comment1"}', u'{"created":"2016-01-01","comment":"comment2"}'] |
| dryer                |                                                   [u'{"created":"2016-01-01","comment":"comment2"}'] |
| front load washer    | [u'{"created":"2016-01-01","comment":"comment1"}', u'{"created":"2016-01-01","comment":"comment2"}'] |
| microwave            |                                                   [u'{"created":"2016-01-01","comment":"comment2"}'] |
| oven                 |                                                   [u'{"created":"2016-01-01","comment":"comment2"}'] |
| refrigerator         |                                                   [u'{"created":"2016-01-01","comment":"comment2"}'] |
| top load washer      | [u'{"created":"2016-01-01","comment":"comment1"}', u'{"created":"2016-01-01","comment":"comment2"}'] |
+----------------------+------------------------------------------------------------------------------------------------------+

To delete repeated value entries, you can use WHERE ... NOT LIKE:

UPDATE dataset.DetailedInventory
SET comments = ARRAY(
  SELECT c FROM UNNEST(comments) AS c
  WHERE c.comment NOT LIKE '%comment2%'
)
WHERE true
+----------------------+----------+--------------------+----------------------------------------------------+----------------+
|       product        | quantity | supply_constrained |                      comments                      | specifications |
+----------------------+----------+--------------------+----------------------------------------------------+----------------+
| countertop microwave |       20 |               NULL |                                                 [] |           NULL |
| dishwasher           |       30 |              false | [u'{"created":"2016-01-01","comment":"comment1"}'] |           NULL |
| dryer                |       30 |              false |                                                 [] |           NULL |
| front load washer    |       20 |              false | [u'{"created":"2016-01-01","comment":"comment1"}'] |           NULL |
| microwave            |       20 |              false |                                                 [] |           NULL |
| oven                 |        5 |              false |                                                 [] |           NULL |
| refrigerator         |       10 |              false |                                                 [] |           NULL |
| top load washer      |       10 |              false | [u'{"created":"2016-01-01","comment":"comment1"}'] |           NULL |
+----------------------+----------+--------------------+----------------------------------------------------+----------------+

UPDATE statement using join between three tables

The following example sets supply_constrained to true for all products from NewArrivals where the warehouse location is in 'WA" state.

UPDATE dataset.DetailedInventory
SET supply_constrained = true
FROM dataset.NewArrivals, dataset.Warehouse
WHERE DetailedInventory.product = NewArrivals.product AND
      NewArrivals.warehouse = Warehouse.warehouse AND
      Warehouse.state = 'WA'

Note that the join predicate for the join with the updated table (DetailedInventory) must be specified using WHERE. However, joins between the other tables (NewArrivals and Warehouse) can be specified using an explicit JOIN ... ON clause. For example, the following query is equivalent to the previous query:

UPDATE dataset.DetailedInventory
SET supply_constrained = true
FROM dataset.NewArrivals
INNER JOIN dataset.Warehouse
ON NewArrivals.warehouse = Warehouse.warehouse
WHERE DetailedInventory.product = NewArrivals.product AND
      Warehouse.state = 'WA'

Before:

DetailedInventory
+----------------------+----------+--------------------+----------+----------------+
|       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 |
+----------------------+----------+--------------------+----------+----------------+
New arrivals
+-----------------+----------+--------------+
|     product     | quantity |  warehouse   |
+-----------------+----------+--------------+
| dryer           |      200 | warehouse #2 |
| oven            |      300 | warehouse #3 |
| top load washer |      100 | warehouse #1 |
+-----------------+----------+--------------+
Warehouse
+--------------+-------+
|  warehouse   | state |
+--------------+-------+
| warehouse #1 | WA    |
| warehouse #2 | CA    |
| warehouse #3 | WA    |
+--------------+-------+

After:

+----------------------+----------+--------------------+----------+----------------+
|       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 |               true |       [] |           NULL |
| refrigerator         |       10 |              false |       [] |           NULL |
| top load washer      |       10 |               true |       [] |           NULL |
+----------------------+----------+--------------------+----------+----------------+

Send feedback about...

BigQuery Documentation