データ操作言語の構文

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`

次の例はテーブルに行を挿入する方法を示しています。サブクエリにより、1 つの数値が計算されています。

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

`STRUCT` 型を含む `INSERT`

次の例は、テーブルに行を挿入する方法を示しています。ここで、一部のフィールドは 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"}} |
+-------------------+----------+--------------------+-------------------------------------------------+----------------------------------------------------------------------------------------------------+

`DELETE` ステートメント

テーブルから行を削除する場合は、DELETE ステートメントを使用します。

DELETE [FROM] target_name [alias]
WHERE condition

`WHERE` キーワード

DELETE ステートメントを作成するごとに、WHERE キーワードと条件を指定する必要があります。

WHERE キーワードは、すべての DELETE ステートメントに必須です。

1 つのテーブル内のすべての行を削除するには、WHERE キーワードに true を指定します。

DELETE FROM dataset.DetailedInventory WHERE true

`DELETE` の例

`WHERE` 句を含む `DELETE`

DELETE dataset.Inventory
WHERE quantity = 0

使用前:

+-------------------+----------+--------------------+
|      product      | quantity | supply_constrained |
+-------------------+----------+--------------------+
| dishwasher        |       20 |               NULL |
| dryer             |       30 |               NULL |
| front load washer |       10 |               NULL |
| microwave         |       20 |               NULL |
| oven              |        5 |               NULL |
| refrigerator      |       10 |               NULL |
| top load washer   |        0 |               NULL |
+-------------------+----------+--------------------+

使用後:

+-------------------+----------+--------------------+
|      product      | quantity | supply_constrained |
+-------------------+----------+--------------------+
| dishwasher        |       20 |               NULL |
| dryer             |       30 |               NULL |
| front load washer |       10 |               NULL |
| microwave         |       20 |               NULL |
| oven              |        5 |               NULL |
| refrigerator      |       10 |               NULL |
+-------------------+----------+--------------------+

サブクエリを使用する `DELETE`

DELETE dataset.Inventory i
WHERE i.product NOT IN (SELECT product from dataset.NewArrivals)

使用前:

Inventory
+-------------------+----------+--------------------+
|      product      | quantity | supply_constrained |
+-------------------+----------+--------------------+
| dishwasher        |       30 |               NULL |
| dryer             |       30 |               NULL |
| front load washer |       20 |               NULL |
| microwave         |       20 |               NULL |
| oven              |        5 |               NULL |
| refrigerator      |       10 |               NULL |
| top load washer   |       10 |               NULL |
+-------------------+----------+--------------------+
NewArrivals
+-----------------+----------+--------------+
|     product     | quantity |  warehouse   |
+-----------------+----------+--------------+
| dryer           |      200 | warehouse #2 |
| oven            |      300 | warehouse #3 |
| top load washer |      100 | warehouse #1 |
+-----------------+----------+--------------+

使用後:

Inventory
+-----------------+----------+--------------------+
|     product     | quantity | supply_constrained |
+-----------------+----------+--------------------+
| dryer           |       30 |               NULL |
| oven            |        5 |               NULL |
| top load washer |       10 |               NULL |
+-----------------+----------+--------------------+

DELETE を EXISTS 句とともに使用することもできます。

DELETE dataset.Inventory
WHERE NOT EXISTS
  (SELECT * from dataset.NewArrivals
   WHERE Inventory.product = NewArrivals.product)

`TRUNCATE TABLE` ステートメント

TRUNCATE TABLE ステートメントは、テーブルからすべての行を削除しますが、テーブルスキーマ、説明、ラベルなどのテーブルのメタデータはそのまま残ります。

TRUNCATE TABLE [[project_name.]dataset_name.]table_name

ここで

project_name は、テーブルを含むプロジェクトの名前です。デフォルトでは、この DDL クエリを実行するプロジェクトの名前が設定されます。
dataset_name は、テーブルを含むデータセットの名前です。
table_name は、切り捨てるテーブルの名前です。

ビュー、マテリアライズドビュー、モデル、外部テーブルの切り捨てはサポートされていません。クエリの割り当てと上限が TRUNCATE TABLE ステートメントに適用されます。詳細については、割り当てと上限をご覧ください。

`TRUNCATE TABLE` の例

次の例では、Inventory という名前のテーブルからすべての行を削除しています。

TRUNCATE TABLE dataset.Inventory

`UPDATE` ステートメント

テーブルの既存の行を更新する場合は、UPDATE ステートメントを使用します。

UPDATE target_name [alias]
SET update_item [, update_item]*
[FROM from_clause]
WHERE condition

update_item ::= column_name = expression

ここで

target_name は、更新するテーブルの名前です。
update_item は、更新する列の名前と更新後の値を評価する式です。

列が STRUCT 型の場合、column_name はドット表記を使用して STRUCT 内のフィールドを参照できます。例: struct1.field1

`WHERE` キーワード

各 UPDATE ステートメントには必ず、WHERE キーワードと条件文を含める必要があります。

テーブル内のすべての行を更新するには、WHERE true とします。

`FROM` キーワード

FROM を使用すると、更新する行を指定できます。SET 式で結合テーブルの列を使用することもできます。

更新する対象のテーブルの行が FROM 句の行と 1 行も結合しない場合、その行は更新されません。

更新する対象のテーブルの行が FROM 句の 1 つの行にのみ結合する場合、その行は更新されます。

更新する対象のテーブルの行が 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 列の繰り返しレコードに 2 番目のエントリを追加します。

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

3 つのテーブル間の結合を使用する `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 を使用することに注意してください。ただし、その他のテーブル（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 ステートメントは、INSERT、UPDATE、DELETE オペレーションを 1 つのステートメントに結合し、オペレーションをアトミックに実行できる DML ステートメントです。

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 の使用を回避します。この最適化は、定数 false 属性といいます。定数 false 属性は、抽出先でアトミックな DELETE を実行するとともに、抽出元の INSERT を実行する場合（DELETE と INSERT の組み合わせは REPLACE オペレーションと呼ばれます）に利用できます。
when_clause

when_clause には 3 つのオプションがあります（MATCHED、NOT MATCHED BY TARGET、NOT MATCHED BY SOURCE）。MERGE ステートメントごとに少なくとも 1 つの when_clause がなければなりません。

when_clause ごとにオプションの search_condition を指定できます。merge_condition と search_condition の両方が満される場合、when_clause が行に対して実行されます。修飾された句が複数ある場合は、行に対して最初の when_clause のみが実行されます。
matched_clause

matched_clause は、行が抽出元テーブルの行に一致したときに、抽出先テーブルの行を更新または削除する方法を定義します。

UPDATE オペレーションを実行する少なくとも 1 つの matched_clause が存在する場合、抽出元テーブルの複数の行が抽出先テーブルの 1 行と一致し、抽出先のその行を更新または削除しようとしたときにランタイムエラーが返されます。
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

次の例のクエリは、倉庫 #1 の商品の数量を NewArrivals テーブル内で 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

STRUCT 型を含む INSERT

DELETE ステートメント

WHERE キーワード

DELETE の例

WHERE 句を含む DELETE

サブクエリを使用する DELETE

TRUNCATE TABLE ステートメント

TRUNCATE TABLE の例

UPDATE ステートメント

WHERE キーワード

FROM キーワード

UPDATE の例

WHERE 句を含む UPDATE

結合を使用する UPDATE

ネストされたフィールドに対する UPDATE

繰り返しレコードに対する UPDATE

3 つのテーブル間の結合を使用する UPDATE ステートメント

MERGE ステートメント

列名を省略する

MERGE の例

例 1

例 2

例 3

例 4

例 5

例 6

例 7

例 8

`INSERT` ステートメント

`INSERT` の例

明示的な値を使用する `INSERT`

`INSERT SELECT` ステートメント

サブクエリを使用する `INSERT VALUES`

列名を指定しない `INSERT`

`STRUCT` 型を含む `INSERT`

`DELETE` ステートメント

`WHERE` キーワード

`DELETE` の例

`WHERE` 句を含む `DELETE`

サブクエリを使用する `DELETE`

`TRUNCATE TABLE` ステートメント

`TRUNCATE TABLE` の例

`UPDATE` ステートメント

`WHERE` キーワード

`FROM` キーワード

`UPDATE` の例

`WHERE` 句を含む `UPDATE`

結合を使用する `UPDATE`

ネストされたフィールドに対する `UPDATE`

繰り返しレコードに対する `UPDATE`

3 つのテーブル間の結合を使用する `UPDATE` ステートメント

`MERGE` ステートメント

`MERGE` の例