借助 BigQuery 数据操纵语言 (DML),您可以在 BigQuery 表中更新、插入和删除数据。
如需了解如何使用 DML 语句,请参阅数据操纵语言。
示例中使用的表
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
语句
如果您想要向表中添加新行,请使用 INSERT
语句。
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
语句必须符合以下规则:
- 如果目标表不是提取时间分区表,则列名称是可选的。
- 目标列的列表中不允许存在重复的名称。
- 必须按照以与指定列相同的顺序添加值。
- 所添加的值的数量必须与指定列的数量相匹配。
- 值的类型必须与目标列兼容。
省略列名称
省略列名称时,目标表中的所有列都将根据其顺序位置按升序排列。请注意,如果目标表是提取时间分区表,则必须指定列名称。
值类型兼容性
使用 INSERT
语句添加的值必须与目标列的类型兼容。只要满足以下条件之一,即可认为值的类型与目标列的类型兼容:
- 值类型与列类型完全匹配。例如,在类型为 INT64 的列中插入 INT64 类型的值。
- 值类型可以隐式强制转换为另一种类型。
INSERT
示例
使用显式值的 INSERT
语句
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
语句
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 | +--------------+-------+
您还可以在使用 INSERT SELECT
时使用 WITH
。例如,您可以使用 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)
以下示例展示了如何将表的内容复制到另一个表中:
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
语句
以下示例展示了如何向表中插入一行,并且其中一个值使用子查询计算得出:
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
语句
INSERT dataset.Warehouse VALUES('warehouse #4', 'WA'), ('warehouse #5', 'NY')
运行查询前的 Warehouse
表如下:
+--------------+-------+ | warehouse | state | +--------------+-------+ | warehouse #1 | WA | | warehouse #2 | CA | | warehouse #3 | WA | +--------------+-------+
运行查询后的 Warehouse
表如下:
+--------------+-------+ | warehouse | state | +--------------+-------+ | warehouse #1 | WA | | warehouse #2 | CA | | warehouse #3 | WA | | warehouse #4 | WA | | warehouse #5 | NY | +--------------+-------+
DELETE
语句
如果您希望从表中删除行,请使用 DELETE
语句。
DELETE [FROM] target_name [alias]
WHERE condition
WHERE
关键字
每次构造 DELETE
语句时,您都必须使用 WHERE
关键字,后接一项条件。
任何 DELETE
语句都必须包含 WHERE
关键字。
如需删除表中的所有行,请将 WHERE
关键字条件设置为 true
:
DELETE FROM dataset.DetailedInventory WHERE true
DELETE
示例
使用 WHERE
子句的 DELETE
语句
DELETE dataset.Inventory
WHERE quantity = 0
之前:
+-------------------+----------+--------------------+ | 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 | +-------------------+----------+--------------------+
之后:
+-------------------+----------+--------------------+ | 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
语句
DELETE dataset.Inventory i
WHERE i.product NOT IN (SELECT product from dataset.NewArrivals)
之前:
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 | +-----------------+----------+--------------+
之后:
Inventory +-----------------+----------+--------------------+ | product | quantity | supply_constrained | +-----------------+----------+--------------------+ | dryer | 30 | NULL | | oven | 5 | NULL | | top load washer | 10 | NULL | +-----------------+----------+--------------------+
您还可以将 DELETE
与 EXISTS
子句配合使用:
DELETE dataset.Inventory
WHERE NOT EXISTS
(SELECT * from dataset.NewArrivals
WHERE Inventory.product = NewArrivals.product)
TRUNCATE TABLE
语句
TRUNCATE TABLE
语句会从表中移除所有行,但会保留表元数据,包括表架构、说明和标签。
TRUNCATE TABLE [[project_name.]dataset_name.]table_name
其中:
project_name
是包含该表的项目的名称。默认为运行此 DDL 查询的项目。dataset_name
是包含该表的数据集的名称。table_name
是要截断的表的名称。
系统不支持截断视图、具体化视图、模型或外部表。查询的配额和限制适用于 TRUNCATE TABLE
语句。如需了解详情,请参阅配额和限制。
TRUNCATE TABLE
示例
以下示例从名为 Inventory
的表中移除所有行。
TRUNCATE TABLE dataset.Inventory
UPDATE
语句
如果您希望更新表中的现有行,请使用 UPDATE
语句。
UPDATE target_name [alias]
SET update_item [, update_item]*
[FROM from_clause]
WHERE condition
update_item ::= path_expression = expression
其中:
target_name
是需要更新的表的名称update_item
包含一个列名和一个更新表达式
WHERE
关键字
每条 UPDATE
语句都必须包含 WHERE
关键字,后接一项条件。
如需更新表中的所有行,请使用 WHERE true
。
FROM
关键字
您可以使用 FROM
指定要更新的行。您还可以在 SET
表达式中使用联接表中的列。
如果要更新的表中的某行与 FROM
子句中的任何行均无联接,则不会更新该行。
如果要更新的表中的某行与 FROM
子句中的一行联接,则更新该行。
如果要更新的表中的某行与 FROM
子句中的多行联接,则查询会生成以下运行时错误:UPDATE/MERGE must
match at most one source row for each target row.
如需指定要更新的表与 FROM
子句中的表之间的联接谓词,请使用 WHERE
子句。如需查看示例,请参阅使用联接的 UPDATE
语句。
UPDATE
示例
使用 WHERE
子句的 UPDATE
语句
在以下示例中,我们将包含字符串 washer
的所有产品的 quantity
字段的值减 10,通过这种方式来更新名为 Inventory
的表:
UPDATE dataset.Inventory
SET quantity = quantity - 10
WHERE product like '%washer%'
之前:
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 | +-------------------+----------+--------------------+
之后:
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
语句
下面的示例:
- 生成一个表,其中包含由现有库存与
NewArrivals
表中的库存构成的总量 - 将
supply_constrained
标记为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)
您还可以联接这两个表:
UPDATE dataset.Inventory i
SET quantity = i.quantity + n.quantity,
supply_constrained = false
FROM dataset.NewArrivals n
WHERE i.product = n.product
之前:
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 | +-----------------+----------+--------------+
之后:
+-------------------+----------+--------------------+ | 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
嵌套字段
下面的示例更新了嵌套的记录字段。
UPDATE dataset.DetailedInventory
SET specifications.color = 'white',
specifications.warranty = '1 year'
WHERE product like '%washer%'
您也可以更新整个记录:
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
重复记录
在以下示例中,我们向包含字符串 washer
的产品的 comments
列中的重复记录附加了一个条目:
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 | +----------------------+----------+--------------------+----------------------------------------------------+----------------+
您也可以使用 ARRAY_CONCAT
函数:
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%'
在以下示例中,我们向所有行的 comments
列中的重复记录附加了第二个条目:
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"}'] | +----------------------+------------------------------------------------------------------------------------------------------+
如需删除重复的值条目,您可以使用 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
语句
以下示例将 NewArrivals
中仓库位置在 'WA'
州的所有产品的 supply_constrained
设置为 true
。
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'
请注意,与更新的表 (DetailedInventory
) 联接时使用的联接谓词必须使用 WHERE
指定。但是,其他表(NewArrivals
和 Warehouse
)之间的联接可以使用显式的 JOIN ... ON
子句指定。例如,以下查询与上一条查询等效:
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'
之前:
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 | +--------------+-------+
之后:
+----------------------+----------+--------------------+----------+----------------+ | 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 | +----------------------+----------+--------------------+----------+----------------+
MERGE
语句
MERGE
语句是一种 DML 语句,它能将 INSERT
、UPDATE
和 DELETE
操作合并成一条语句,并以原子方式执行这些操作。
MERGE [INTO] target_name [[AS] alias]
USING source_name
ON merge_condition
{ when_clause } +
when_clause ::= matched_clause | not_matched_by_target_clause | not_matched_by_source_clause
matched_clause ::= WHEN MATCHED [ AND search_condition ] THEN { merge_update_clause | merge_delete_clause }
not_matched_by_target_clause ::= WHEN NOT MATCHED [BY TARGET] [ AND search_condition ] THEN merge_insert_clause
not_matched_by_source_clause ::= WHEN NOT MATCHED BY SOURCE [ AND search_condition ] THEN { merge_update_clause | merge_delete_clause }
merge_condition ::= bool_expression
search_condition ::= bool_expression
merge_update_clause ::= UPDATE SET update_item [, update_item]*
update_item ::= path_expression = expression
merge_delete_clause ::= DELETE
merge_insert_clause ::= INSERT [(column_1 [, ..., column_n ])] input
input ::= VALUES (expr_1 [, ..., expr_n ]) | ROW
expr ::= expression | DEFAULT
其中:
target_name
target_name
是您要更改的表的名称。
source_name
source_name
是表名称或子查询。
merge_condition
MERGE
语句在目标与源之间执行JOIN
操作。然后,系统会根据匹配状态(匹配的行、仅在源表中或仅在目标表中)执行相应的WHEN
子句。merge_condition
供JOIN
用于在源表与目标表之间进行行匹配。根据WHEN
子句的组合,应用不同的INNER
和OUTER
JOIN
类型。如果 merge_condition 为
FALSE
,则查询优化器会避免使用JOIN
。此优化即称为常量假谓词。如果您对目标执行原子DELETE
,此外还要从源执行INSERT
(使用INSERT
的DELETE
也称为REPLACE
操作),则常量假谓词十分有用。
when_clause
when_clause
有三个选项:MATCHED
、NOT MATCHED BY TARGET
和NOT MATCHED BY SOURCE
。每条MERGE
语句中必须至少有一个when_clause
。每条
when_clause
可以有一个可选的search_condition
。如果同时满足了merge_condition
和search_condition
,则针对相应行执行when_clause
。如果存在多条限定子句,则仅为相应行执行第一条when_clause
。
matched_clause
matched_clause
定义了在目标表中的某行与源表中的一行匹配时,如何对目标表中的行进行更新或删除。如果至少有一条
matched_clause
执行UPDATE
操作,则在源表中有多行与目标表中的一行匹配,并且您试图更新或删除目标表中的该行时,系统会返回运行时错误。
not_matched_by_target_clause
not_matched_by_target_clause
定义了在源表中的某行与目标表中的任何行均不匹配的情况下,应如何插入目标表。
not_matched_by_source_clause
not_matched_by_source_clause
定义了在目标表中的某行与源表中的任何行均不匹配的情况下,应如何更新或删除目标表中的该行。
省略列名称
- 在
not_matched_by_target_clause
中省略目标表的列名称时,目标表中的所有列都将根据其顺序位置按升序排列。请注意,如果目标表是提取时间分区表,则必须指定列名称。 - 在
not_matched_by_target_clause
中,ROW
可用于包括源中根据顺序位置按升序排列的所有列。请注意,源的伪列不包括在内。例如,当源是提取时间分区表时,伪列_PARTITIONTIME
不包括在内。
MERGE
示例
示例 1
在以下示例中,查询会将 Inventory
表中的新产品添加到 DetailedInventory
表中。对于库存量较少的产品,supply_constrained
值会设置为 true
,并且会添加注释。
MERGE dataset.DetailedInventory T
USING dataset.Inventory S
ON T.product = S.product
WHEN NOT MATCHED AND quantity < 20 THEN
INSERT(product, quantity, supply_constrained, comments)
VALUES(product, quantity, true, ARRAY<STRUCT<created DATE, comment STRING>>[(DATE('2016-01-01'), 'comment1')])
WHEN NOT MATCHED THEN
INSERT(product, quantity, supply_constrained)
VALUES(product, quantity, false)
运行查询之前的表如下:
Inventory +-------------------+----------+ | product | quantity | +-------------------+----------+ | dishwasher | 30 | | dryer | 30 | | front load washer | 20 | | microwave | 20 | | oven | 5 | | top load washer | 10 | +-------------------+----------+
DetailedInventory +----------------------+----------+--------------------+----------+----------------+ | product | quantity | supply_constrained | comments | specifications | +----------------------+----------+--------------------+----------+----------------+ | countertop microwave | 20 | NULL | [] | NULL | | front load washer | 20 | false | [] | NULL | | microwave | 20 | false | [] | NULL | | refrigerator | 10 | false | [] | NULL | +----------------------+----------+--------------------+----------+----------------+
运行查询后的 DetailedInventory
表如下:
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 | true | [{"created":"2016-01-01","comment":"comment1"}] | NULL | | refrigerator | 10 | false | [] | NULL | | top load washer | 10 | true | [{"created":"2016-01-01","comment":"comment1"}] | NULL | +----------------------+----------+--------------------+-------------------------------------------------+----------------+
示例 2
在以下示例中,查询会将 NewArrivals
表中的项合并到 Inventory
表中。如果某个项在 Inventory
中已存在,则查询将递增 quantity
字段。否则,查询将插入新行。
MERGE dataset.Inventory T
USING dataset.NewArrivals S
ON T.product = S.product
WHEN MATCHED THEN
UPDATE SET quantity = T.quantity + S.quantity
WHEN NOT MATCHED THEN
INSERT (product, quantity) VALUES(product, quantity)
运行查询之前的表如下:
NewArrivals +-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 20 | warehouse #2 | | oven | 30 | warehouse #3 | | refrigerator | 25 | warehouse #2 | | top load washer | 10 | warehouse #1 | +-----------------+----------+--------------+
Inventory +-------------------+----------+ | product | quantity | +-------------------+----------+ | dishwasher | 30 | | dryer | 30 | | front load washer | 20 | | microwave | 20 | | oven | 5 | | top load washer | 10 | +-------------------+----------+
运行查询后的 Inventory
表如下:
Inventory +-------------------+----------+ | product | quantity | +-------------------+----------+ | dishwasher | 30 | | dryer | 50 | | front load washer | 20 | | microwave | 20 | | oven | 35 | | refrigerator | 25 | | top load washer | 20 | +-------------------+----------+
示例 3
在以下示例中,查询将 NewArrivals
表中来自仓库 1 的产品数量增加 20。除了仓库 2 中的产品之外,查询会删除其他所有产品。
MERGE dataset.NewArrivals T
USING (SELECT * FROM dataset.NewArrivals WHERE warehouse <> 'warehouse #2') S
ON T.product = S.product
WHEN MATCHED AND T.warehouse = 'warehouse #1' THEN
UPDATE SET quantity = T.quantity + 20
WHEN MATCHED THEN
DELETE
运行查询前的 NewArrivals
表如下:
NewArrivals +-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 20 | warehouse #2 | | oven | 30 | warehouse #3 | | refrigerator | 25 | warehouse #2 | | top load washer | 10 | warehouse #1 | +-----------------+----------+--------------+
运行查询后的 NewArrivals
表如下:
NewArrivals +-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 20 | warehouse #2 | | refrigerator | 25 | warehouse #2 | | top load washer | 30 | warehouse #1 | +-----------------+----------+--------------+
示例 4
在以下示例中,查询会使用 NewArrivals
表中的值替换 Inventory
表中类似于 '%washer%'
的所有产品。
MERGE dataset.Inventory T
USING dataset.NewArrivals S
ON FALSE
WHEN NOT MATCHED AND product LIKE '%washer%' THEN
INSERT (product, quantity) VALUES(product, quantity)
WHEN NOT MATCHED BY SOURCE AND product LIKE '%washer%' THEN
DELETE
运行查询之前的表如下:
NewArrivals +-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 20 | warehouse #2 | | refrigerator | 25 | warehouse #2 | | top load washer | 30 | warehouse #1 | +-----------------+----------+--------------+
Inventory +-------------------+----------+ | product | quantity | +-------------------+----------+ | dishwasher | 30 | | dryer | 50 | | front load washer | 20 | | microwave | 20 | | oven | 35 | | refrigerator | 25 | | top load washer | 20 | +-------------------+----------+
运行查询后的 Inventory
表如下:
Inventory +-----------------+----------+ | product | quantity | +-----------------+----------+ | dryer | 50 | | microwave | 20 | | oven | 35 | | refrigerator | 25 | | top load washer | 30 | +-----------------+----------+
示例 5
在以下示例中,如果产品数量少于 Inventory
表中的平均数量,则查询会在 DetailedInventory
表中添加一条注释。
MERGE dataset.DetailedInventory T
USING dataset.Inventory S
ON T.product = S.product
WHEN MATCHED AND S.quantity < (SELECT AVG(quantity) FROM dataset.Inventory) THEN
UPDATE SET comments = ARRAY_CONCAT(comments, ARRAY<STRUCT<created DATE, comment STRING>>[(CAST('2016-02-01' AS DATE), 'comment2')])
运行查询之前的表如下:
Inventory +-----------------+----------+ | product | quantity | +-----------------+----------+ | dryer | 50 | | microwave | 20 | | oven | 35 | | refrigerator | 25 | | top load washer | 30 | +-----------------+----------+
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 | true | [{"created":"2016-01-01","comment":"comment1"}] | NULL | | refrigerator | 10 | false | [] | NULL | | top load washer | 10 | true | [{"created":"2016-01-01","comment":"comment1"}] | NULL | +----------------------+----------+--------------------+-------------------------------------------------+----------------+
运行查询后的 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 | [{"created":"2016-02-01","comment":"comment2"}] | NULL | | oven | 5 | true | [{"created":"2016-01-01","comment":"comment1"}] | NULL | | refrigerator | 10 | false | [{"created":"2016-02-01","comment":"comment2"}] | NULL | | top load washer | 10 | true | [{"created":"2016-01-01","comment":"comment1"},{"created":"2016-02-01","comment":"comment2"}] | NULL | +----------------------+----------+--------------------+-----------------------------------------------------------------------------------------------+----------------+
示例 6
在以下示例中,查询会增加位于 CA
的仓库中的产品库存。来自其他州的产品被删除,而任何未出现在 NewArrivals
表中的产品均保持不变。
MERGE dataset.Inventory T
USING (SELECT product, quantity, state FROM dataset.NewArrivals t1 JOIN dataset.Warehouse t2 ON t1.warehouse = t2.warehouse) S
ON T.product = S.product
WHEN MATCHED AND state = 'CA' THEN
UPDATE SET quantity = T.quantity + S.quantity
WHEN MATCHED THEN
DELETE
运行查询之前的表如下:
Warehouse +--------------+-------+ | warehouse | state | +--------------+-------+ | warehouse #1 | WA | | warehouse #2 | CA | | warehouse #3 | WA | +--------------+-------+
NewArrivals +-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 20 | warehouse #2 | | refrigerator | 25 | warehouse #2 | | top load washer | 30 | warehouse #1 | +-----------------+----------+--------------+
Inventory +-----------------+----------+ | product | quantity | +-----------------+----------+ | dryer | 50 | | microwave | 20 | | oven | 35 | | refrigerator | 25 | | top load washer | 30 | +-----------------+----------+
运行查询后的 Inventory
表如下:
Inventory +--------------+----------+ | product | quantity | +--------------+----------+ | dryer | 70 | | microwave | 20 | | oven | 35 | | refrigerator | 50 | +--------------+----------+
示例 7
在以下示例中,系统会返回运行时错误,因为如果源包含多个匹配行,查询会尝试更新目标表。如需解决该错误,您需要更改 merge_condition
或 search_condition
以避免源中出现重复的匹配行。
MERGE dataset.Inventory T
USING dataset.NewArrivals S
ON T.product = S.product
WHEN MATCHED THEN
UPDATE SET quantity = T.quantity + S.quantity
运行查询之前的表如下:
NewArrivals +-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 10 | warehouse #2 | | dryer | 20 | warehouse #1 | | refrigerator | 25 | warehouse #2 | | top load washer | 30 | warehouse #1 | +-----------------+----------+--------------+
Inventory +--------------+----------+ | product | quantity | +--------------+----------+ | dryer | 70 | | microwave | 20 | | oven | 35 | | refrigerator | 50 | +--------------+----------+
运行查询时,系统会返回以下错误:
UPDATE/MERGE must match at most one source row for each target row
示例 8
在以下示例中,NewArrivals
表中的所有产品均替换为子查询中的值。INSERT
子句不指定目标表和源子查询的列名称。
MERGE dataset.NewArrivals
USING (SELECT * FROM UNNEST([('microwave', 10, 'warehouse #1'),
('dryer', 30, 'warehouse #1'),
('oven', 20, 'warehouse #2')]))
ON FALSE
WHEN NOT MATCHED THEN
INSERT ROW
WHEN NOT MATCHED BY SOURCE THEN
DELETE
运行查询前的 NewArrivals
表如下:
+-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 10 | warehouse #2 | | dryer | 20 | warehouse #1 | | refrigerator | 25 | warehouse #2 | | top load washer | 30 | warehouse #1 | +-----------------+----------+--------------+
运行查询后的 NewArrivals
表如下:
+-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | microwave | 10 | warehouse #1 | | dryer | 30 | warehouse #1 | | oven | 20 | warehouse #2 | +-----------------+----------+--------------+