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 | +--------------+-------+
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 ::= path_expression = expression
ここで
target_name
は、更新するテーブルの名前です。update_item
は、列の名前と更新の式で構成されます。
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 ::= path_expression = expression
merge_delete_clause ::= DELETE
merge_insert_clause ::= INSERT [(column_1 [, ..., column_n ])] input
input ::= VALUES (expr_1 [, ..., expr_n ]) | ROW
expr ::= expression | DEFAULT
ここで
target_name
target_name
は、変更するテーブルの名前です。
source_name
source_name
は、テーブル名またはサブクエリです。
merge_condition
MERGE
ステートメントは、抽出先と抽出元の間でJOIN
を実行します。次に、一致ステータス(行一致、抽出元テーブルのみ、抽出先テーブルのみ)に応じて、対応するWHEN
句が実行されます。merge_condition
は、抽出元テーブルと抽出先テーブルの間で行を照合するために、JOIN
によって使用されます。WHEN
句の組み合わせによって、異なるINNER
とOUTER
JOIN
タイプが適用されます。merge_condition が
FALSE
の場合、クエリ オプティマイザーはJOIN
の使用を回避します。この最適化は、定数 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 | +-----------------+----------+--------------+