数据操纵语言语法

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

`INSERT` 有 `STRUCT` 个类型

以下示例展示了如何向表中插入一行，其中一些字段为 STRUCT 类型。

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

运行查询后的 DetailedInventory 表如下：

+-------------------+----------+--------------------+-------------------------------------------------+----------------------------------------------------------------------------------------------------+
|      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` 有 `ARRAY` 个类型

以下示例展示了如何向表中插入一行，其中一个字段是 ARRAY 类型。

CREATE TABLE IF NOT EXISTS dataset.table1 (names ARRAY<STRING>);

INSERT INTO dataset.table1 (names)
VALUES (["name1","name2"])

运行查询后的表如下：

+-------------------+
|       names       |
+-------------------+
| ["name1","name2"] |
+-------------------+

`DELETE` 语句

如果您希望从表中删除行，请使用 DELETE 语句。

DELETE [FROM] target_name [alias]
WHERE condition

如需删除表中的所有行，请使用 TRUNCATE TABLE 语句。

`WHERE` 关键字

每次构造 DELETE 语句时，您都必须使用 WHERE 关键字，后接一项条件。

任何 DELETE 语句都必须包含 WHERE 关键字。

`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 语句将失败。如需清空表，请先更新表以移除分区过滤条件要求，然后再运行 TRUNCATE TABLE 语句。如需了解详情，请参阅更新分区过滤条件要求。

`TRUNCATE TABLE` 示例

以下示例从名为 Inventory 的表中移除所有行。

TRUNCATE TABLE dataset.Inventory

`UPDATE` 语句

如果您希望更新表中的现有行，请使用 UPDATE 语句。

UPDATE target_name [[AS] alias]
SET set_clause
[FROM from_clause]
WHERE condition

set_clause ::= update_item[, ...]

update_item ::= column_name = expression

其中：

target_name 是需要更新的表的名称。
update_item 是需要更新的列的名称，以及一个针对更新后的值进行评估的表达式。

如果列是 STRUCT 类型，则 column_name 可以使用点表示法引用 STRUCT 中的字段。例如 struct1.field1。

`WHERE` 关键字

每条 UPDATE 语句都必须包含 WHERE 关键字，后接一项条件。

如需更新表中的所有行，请使用 WHERE true。

`FROM` 关键字

您可以选择在 UPDATE 语句中添加 FROM 子句。

您可以使用 FROM 子句指定目标表中要更新的行。您还可以在 SET 子句或 WHERE 条件中使用联接表中的列。

如果 WHERE 子句中未指定条件，则 FROM 子句联接可以是交叉联接，否则为内联接。在任一情况下，目标表中的行最多只能与 FROM 子句中的一行联接。

如需指定要更新的表与 FROM 子句中的表之间的联接谓词，请使用 WHERE 子句。如需查看示例，请参阅使用联接的 UPDATE 语句。

注意事项：

SET 子句可以引用目标表中的列以及 FROM 子句中的任何 FROM 项的列。如果存在名称冲突，则非限定引用会被视为模糊引用。
如果目标表以表名称的形式出现在 FROM 子句中，则它必须具有别名才能执行自联接。
如果要更新的表中的某行与 FROM 子句中的任何行均无联接，则不会更新该行。
如果要更新的表中的某行与 FROM 子句中的一行联接，则更新该行。
如果要更新的表中的某行与 FROM 子句中的多行联接，则查询会生成以下运行时错误：UPDATE/MERGE must match at most one source row for each target row.

`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 ::= column_name = 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 |
+-----------------+----------+--------------+

数据操纵语言语法

示例中使用的表

INSERT 语句

省略列名称

值类型兼容性

INSERT 示例

使用显式值的 INSERT 语句

INSERT SELECT 语句

使用子查询的 INSERT VALUES 语句

无列名称的 INSERT 语句

INSERT 有 STRUCT 个类型

INSERT 有 ARRAY 个类型

DELETE 语句

WHERE 关键字

DELETE 示例

使用 WHERE 子句的 DELETE 语句

使用子查询的 DELETE 语句

TRUNCATE TABLE 语句

TRUNCATE TABLE 示例

UPDATE 语句

WHERE 关键字

FROM 关键字

UPDATE 示例

使用 WHERE 子句的 UPDATE 语句

使用联接的 UPDATE 语句

UPDATE 嵌套字段

UPDATE 重复记录

使用三个表之间的联接的 UPDATE 语句

MERGE 语句

省略列名称

MERGE 示例

示例 1

示例 2

示例 3

示例 4

示例 5

示例 6

示例 7

示例 8

`INSERT` 语句

`INSERT` 示例

使用显式值的 `INSERT` 语句

`INSERT SELECT` 语句

使用子查询的 `INSERT VALUES` 语句

无列名称的 `INSERT` 语句

`INSERT` 有 `STRUCT` 个类型

`INSERT` 有 `ARRAY` 个类型

`DELETE` 语句

`WHERE` 关键字

`DELETE` 示例

使用 `WHERE` 子句的 `DELETE` 语句

使用子查询的 `DELETE` 语句

`TRUNCATE TABLE` 语句

`TRUNCATE TABLE` 示例

`UPDATE` 语句

`WHERE` 关键字

`FROM` 关键字

`UPDATE` 示例

使用 `WHERE` 子句的 `UPDATE` 语句

使用联接的 `UPDATE` 语句

`UPDATE` 嵌套字段

`UPDATE` 重复记录

使用三个表之间的联接的 `UPDATE` 语句

`MERGE` 语句

`MERGE` 示例