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_nameis the name of a table to updateupdate_itemcomprises 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
NewArrivalstable, and - Marks
supply_constrainedasfalse:
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 | +----------------------+----------+--------------------+----------+----------------+
