資料操縱語言語法

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 關鍵字,後接條件。

WHERE 關鍵字對任何 DELETE 陳述式而言都是必要條件。

如要刪除資料表中的所有資料列,請將 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)

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

以下範例會更新名為 Inventory 的資料表,針對所有包含 washer 字串的產品,將 quantity 欄位的值減 10:

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 加以指定。但其他資料表 (NewArrivalsWarehouse) 之間的彙整可以使用明確的 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 陳述式,可以將 INSERTUPDATEDELETE 作業結合成一個陳述式,並以不可分割的形式執行這些作業。

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_conditon ::= 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 子句。JOIN 會使用 merge_condition 來比對來源和目標資料表的資料列。再依據 WHEN 子句的組合,套用不同的 INNEROUTER JOIN 類型。

    如果 merge_condition 是 FALSE,則查詢最佳化工具會避免使用 JOIN。這種最佳化稱為常數假述詞。當您要對目標執行不可部分完成的 DELETEINSERT 來源的資料時,常數假述詞非常有用 (DELETE 搭配 INSERT 也稱為 REPLACE 作業)。

  • when_clause

    when_clause 提供三個選項:MATCHEDNOT MATCHED BY TARGETNOT MATCHED BY SOURCE。每個 MERGE 陳述式中至少必須有一個 when_clause

    每個 when_clause 都可以選用一個 search_condition。在同時滿足 merge_conditionsearch_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

在下例中,查詢會將新項目新增至 Inventory 資料表,並更新 NewArrivals 資料表中現有產品的數量。

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_conditionsearch_conditon,避免來源出現重複的相符項目。

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