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)
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_nametarget_nameは、変更するテーブルの名前です。
source_namesource_nameは、テーブル名またはサブクエリです。
merge_conditionMERGEステートメントは、抽出先と抽出元の間でJOINを実行します。次に、一致ステータス（行一致、抽出元テーブルのみ、抽出先テーブルのみ）に応じて、対応するWHEN句が実行されます。merge_conditionは、抽出元テーブルと抽出先テーブルの間で行を照合するために、JOINによって使用されます。WHEN句の組み合わせによって、異なるINNERとOUTERJOINタイプが適用されます。merge_condition が
FALSEの場合、クエリ オプティマイザーはJOINの使用を回避します。この最適化は、定数 false 属性といいます。定数 false 属性は、抽出先でアトミックなDELETEを実行するとともに、抽出元のINSERTを実行する場合（DELETEとINSERTの組み合わせはREPLACEオペレーションと呼ばれます）に利用できます。
when_clausewhen_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_clausematched_clauseは、行が抽出元テーブルの行に一致したときに、抽出先テーブルの行を更新または削除する方法を定義します。UPDATEオペレーションを実行する少なくとも 1 つのmatched_clauseが存在する場合、抽出元テーブルの複数の行が抽出先テーブルの 1 行と一致し、抽出先のその行を更新または削除しようとしたときにランタイム エラーが返されます。
not_matched_by_target_clausenot_matched_by_target_clauseは、抽出元テーブルの行が抽出先テーブルの行に一致しない場合に、抽出先テーブルに挿入する方法を定義します。
not_matched_by_source_clausenot_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
次の例のクエリは、倉庫 #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 | +-----------------+----------+--------------+