資料操縱語言語法

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 陳述式

以下範例針對倉儲位置在 'WA' 州的所有 NewArrivals 產品,將 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。這種最佳化稱為常數假述詞。當您要對目標執行不可部分完成的 DELETE 以及來源的 INSERT 時,常數假述詞非常有用 (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 |
+-----------------+----------+--------------+
本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁