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 キーワードを使用して条件をつける必要があります。
DELETE ステートメントは、いずれの場合も WHERE キーワードが必須です。
1 つのテーブル内のすべての行を削除するには、WHERE キーワードを true にします。
DELETE FROM dataset.DetailedInventory WHERE true
DELETE の例
DELETE と WHERE 句
DELETE dataset.Inventory
WHERE quantity = 0
使用前:
+-------------------+----------+--------------------+ | product | quantity | supply_constrained | +-------------------+----------+--------------------+ | dishwasher | 20 | NULL | | dryer | 30 | NULL | | front load washer | 10 | NULL | | microwave | 20 | NULL | | oven | 5 | NULL | | refrigerator | 10 | NULL | | top load washer | 0 | NULL | +-------------------+----------+--------------------+
使用後:
+-------------------+----------+--------------------+ | product | quantity | supply_constrained | +-------------------+----------+--------------------+ | dishwasher | 20 | NULL | | dryer | 30 | NULL | | front load washer | 10 | NULL | | microwave | 20 | NULL | | oven | 5 | NULL | | refrigerator | 10 | NULL | +-------------------+----------+--------------------+
サブクエリを使用した DELETE
DELETE dataset.Inventory i
WHERE i.product NOT IN (SELECT product from dataset.NewArrivals)
使用前:
Inventory +-------------------+----------+--------------------+ | product | quantity | supply_constrained | +-------------------+----------+--------------------+ | dishwasher | 30 | NULL | | dryer | 30 | NULL | | front load washer | 20 | NULL | | microwave | 20 | NULL | | oven | 5 | NULL | | refrigerator | 10 | NULL | | top load washer | 10 | NULL | +-------------------+----------+--------------------+ NewArrivals +-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 200 | warehouse #2 | | oven | 300 | warehouse #3 | | top load washer | 100 | warehouse #1 | +-----------------+----------+--------------+
使用後:
Inventory +-----------------+----------+--------------------+ | product | quantity | supply_constrained | +-----------------+----------+--------------------+ | dryer | 30 | NULL | | oven | 5 | NULL | | top load washer | 10 | NULL | +-----------------+----------+--------------------+
DELETE に EXISTS 句を使用することもできます。
DELETE dataset.Inventory
WHERE NOT EXISTS
(SELECT * from dataset.NewArrivals
WHERE Inventory.product = NewArrivals.product)
UPDATE ステートメント
テーブル内の既存の行を更新するには、UPDATE ステートメントを使用します。
UPDATE target_name [alias]
SET update_item [, update_item]*
[FROM from_clause]
WHERE condition
update_item ::= path_expression = expression
ここで
target_nameは更新するテーブルの名前。update_itemは列の名前と更新の式からなる。
WHERE キーワード
各 UPDATE ステートメントには WHERE キーワードと条件を付ける必要があります。
テーブル内の行を更新するには、WHERE true を使用します。
FROM キーワード
FROM を使用すると、更新する行を指定できます。SET 式内に結合したテーブルの列を使用することもできます。
テーブル内で更新する行が FROM 句でゼロ個の行に結合する場合は、その行は更新されません。
テーブル内で更新する行が FROM 句の 1 つの行にのみ結合する場合は、その行が更新されます。
テーブル内で更新する行が FROM 句の複数の行に結合する場合、そのクエリではランタイム エラー UPDATE/MERGE must match at
most one source row for each target row. が発生します。
更新するテーブルと FROM 句のテーブルの間に結合属性を指定するには、WHERE 句を使用します。この例は結合を使用した UPDATE をご覧ください。
UPDATE の例
UPDATE と WHERE 句
次の例では 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 |
+----------------------+----------+--------------------+----------------------------------------------------+----------------+
UPDATE ステートメントで 3 つのテーブルを結合する
次の例では、'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_conditon ::= bool_expression
merge_update_clause ::= UPDATE SET update_item [, update_item]*
update_item ::= path_expression = expression
merge_delete_clause ::= DELETE
merge_insert_clause ::= INSERT [(column_1 [, ..., column_n ])] input
input ::= VALUES (expr_1 [, ..., expr_n ]) | ROW
expr ::= expression | DEFAULT
ここで
target_nametarget_nameは、変更するテーブルの名前です。
source_namesource_nameは、テーブル名またはサブクエリです。
merge_conditionMERGEステートメントは、抽出先と抽出元の間でJOINを実行します。次に、一致状態（行一致、抽出元テーブルのみ、抽出先テーブルのみ）に応じて、対応するWHEN句が実行されます。merge_conditionがJOINによって使用され、抽出元テーブルと抽出先テーブル間で行が比較されます。WHEN句の組み合わせによって、異なるINNERとOUTERJOINタイプが適用されます。merge_condition が
FALSEの場合、クエリ オプティマイザはJOINの使用を回避します。この最適化は、常時 false 述語といいます。抽出先にアトミックなDELETEを実行するだけでなく、抽出元からINSERTを実行する場合（DELETEとINSERTの組み合わせはREPLACEオペレーションといいます）、常時 false 述語は便利です。
when_clausewhen_clause句には、MATCHED、NOT MATCHED BY TARGET、NOT MATCHED BY SOURCEの 3 つのオプションがあります。各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
次の例のクエリでは、NewArrivals テーブルで、倉庫番号 1 の商品の数量を 20 個増やします。このクエリでは、倉庫番号 2 の商品を除く、すべての商品を削除します。
MERGE dataset.NewArrivals T
USING (SELECT * FROM dataset.NewArrivals WHERE warehouse <> 'warehouse #2') S
ON T.product = S.product
WHEN MATCHED AND T.warehouse = 'warehouse #1' THEN
UPDATE SET quantity = T.quantity + 20
WHEN MATCHED THEN
DELETE
クエリを実行する前の NewArrivals テーブルは次のとおりです。
NewArrivals +-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 20 | warehouse #2 | | oven | 30 | warehouse #3 | | refrigerator | 25 | warehouse #2 | | top load washer | 10 | warehouse #1 | +-----------------+----------+--------------+
クエリを実行した後の NewArrivals テーブルは次のとおりです。
NewArrivals +-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 20 | warehouse #2 | | refrigerator | 25 | warehouse #2 | | top load washer | 30 | warehouse #1 | +-----------------+----------+--------------+
例 4
次の例のクエリでは、NewArrivals テーブルの値を使用して、Inventory テーブルのすべての商品（'%washer%' など）を置換します。
MERGE dataset.Inventory T
USING dataset.NewArrivals S
ON FALSE
WHEN NOT MATCHED AND product LIKE '%washer%' THEN
INSERT (product, quantity) VALUES(product, quantity)
WHEN NOT MATCHED BY SOURCE AND product LIKE '%washer%' THEN
DELETE
クエリを実行する前のテーブルは次のとおりです。
NewArrivals +-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 20 | warehouse #2 | | refrigerator | 25 | warehouse #2 | | top load washer | 30 | warehouse #1 | +-----------------+----------+--------------+
Inventory +-------------------+----------+ | product | quantity | +-------------------+----------+ | dishwasher | 30 | | dryer | 50 | | front load washer | 20 | | microwave | 20 | | oven | 35 | | refrigerator | 25 | | top load washer | 20 | +-------------------+----------+
クエリを実行した後の Inventory テーブルは次のとおりです。
Inventory +-----------------+----------+ | product | quantity | +-----------------+----------+ | dryer | 50 | | microwave | 20 | | oven | 35 | | refrigerator | 25 | | top load washer | 30 | +-----------------+----------+
例 5
次の例のクエリでは、商品数が Inventory テーブルの平均数量よりも少ない場合に、DetailedInventory テーブルにコメントを追加します。
MERGE dataset.DetailedInventory T
USING dataset.Inventory S
ON T.product = S.product
WHEN MATCHED AND S.quantity < (SELECT AVG(quantity) FROM dataset.Inventory) THEN
UPDATE SET comments = ARRAY_CONCAT(comments, ARRAY<STRUCT<created DATE, comment STRING>>[(CAST('2016-02-01' AS DATE), 'comment2')])
クエリを実行する前のテーブルは次のとおりです。
Inventory +-----------------+----------+ | product | quantity | +-----------------+----------+ | dryer | 50 | | microwave | 20 | | oven | 35 | | refrigerator | 25 | | top load washer | 30 | +-----------------+----------+
DetailedInventory
+----------------------+----------+--------------------+-------------------------------------------------+----------------+
| product | quantity | supply_constrained | comments | specifications |
+----------------------+----------+--------------------+-------------------------------------------------+----------------+
| countertop microwave | 20 | NULL | [] | NULL |
| dishwasher | 30 | false | [] | NULL |
| dryer | 30 | false | [] | NULL |
| front load washer | 20 | false | [] | NULL |
| microwave | 20 | false | [] | NULL |
| oven | 5 | true | [{"created":"2016-01-01","comment":"comment1"}] | NULL |
| refrigerator | 10 | false | [] | NULL |
| top load washer | 10 | true | [{"created":"2016-01-01","comment":"comment1"}] | NULL |
+----------------------+----------+--------------------+-------------------------------------------------+----------------+
クエリを実行した後の DetailedInventory テーブルは次のとおりです。
+----------------------+----------+--------------------+-----------------------------------------------------------------------------------------------+----------------+
| product | quantity | supply_constrained | comments | specifications |
+----------------------+----------+--------------------+-----------------------------------------------------------------------------------------------+----------------+
| countertop microwave | 20 | NULL | [] | NULL |
| dishwasher | 30 | false | [] | NULL |
| dryer | 30 | false | [] | NULL |
| front load washer | 20 | false | [] | NULL |
| microwave | 20 | false | [{"created":"2016-02-01","comment":"comment2"}] | NULL |
| oven | 5 | true | [{"created":"2016-01-01","comment":"comment1"}] | NULL |
| refrigerator | 10 | false | [{"created":"2016-02-01","comment":"comment2"}] | NULL |
| top load washer | 10 | true | [{"created":"2016-01-01","comment":"comment1"},{"created":"2016-02-01","comment":"comment2"}] | NULL |
+----------------------+----------+--------------------+-----------------------------------------------------------------------------------------------+----------------+
例 6
次の例のクエリでは、CA にある倉庫の在庫数を増やします。他の州にある製品は削除しますが、NewArrivals テーブルにない商品は変更しません。
MERGE dataset.Inventory T
USING (SELECT product, quantity, state FROM dataset.NewArrivals t1 JOIN dataset.Warehouse t2 ON t1.warehouse = t2.warehouse) S
ON T.product = S.product
WHEN MATCHED AND state = 'CA' THEN
UPDATE SET quantity = T.quantity + S.quantity
WHEN MATCHED THEN
DELETE
クエリを実行する前のテーブルは次のとおりです。
Warehouse +--------------+-------+ | warehouse | state | +--------------+-------+ | warehouse #1 | WA | | warehouse #2 | CA | | warehouse #3 | WA | +--------------+-------+
NewArrivals +-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 20 | warehouse #2 | | refrigerator | 25 | warehouse #2 | | top load washer | 30 | warehouse #1 | +-----------------+----------+--------------+
Inventory +-----------------+----------+ | product | quantity | +-----------------+----------+ | dryer | 50 | | microwave | 20 | | oven | 35 | | refrigerator | 25 | | top load washer | 30 | +-----------------+----------+
クエリを実行した後の Inventory テーブルは次のとおりです。
Inventory +--------------+----------+ | product | quantity | +--------------+----------+ | dryer | 70 | | microwave | 20 | | oven | 35 | | refrigerator | 50 | +--------------+----------+
例 7
次の例では、一致する行が抽出元に複数存在するときにクエリで抽出先テーブルの更新を試みたため、ランタイム エラーが返されます。このエラーを解決するには、merge_condition または search_conditon を変更して、抽出元に重複する一致が存在しないようにする必要があります。
MERGE dataset.Inventory T
USING dataset.NewArrivals S
ON T.product = S.product
WHEN MATCHED THEN
UPDATE SET quantity = T.quantity + S.quantity
クエリを実行する前のテーブルは次のとおりです。
NewArrivals +-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 10 | warehouse #2 | | dryer | 20 | warehouse #1 | | refrigerator | 25 | warehouse #2 | | top load washer | 30 | warehouse #1 | +-----------------+----------+--------------+
Inventory +--------------+----------+ | product | quantity | +--------------+----------+ | dryer | 70 | | microwave | 20 | | oven | 35 | | refrigerator | 50 | +--------------+----------+
クエリを実行すると、次のエラーが返されます。
UPDATE/MERGE must match at most one source row for each target row
例 8
次の例では、NewArrivals テーブル内のすべての製品がサブクエリの値に置き換えられます。INSERT 句は、ターゲット テーブルまたはソースのサブクエリのどちらの列名も指定していません。
MERGE dataset.NewArrivals
USING (SELECT * FROM UNNEST([('microwave', 10, 'warehouse #1'),
('dryer', 30, 'warehouse #1'),
('oven', 20, 'warehouse #2')]))
ON FALSE
WHEN NOT MATCHED THEN
INSERT ROW
WHEN NOT MATCHED BY SOURCE THEN
DELETE
クエリを実行する前の NewArrivals テーブルは次のとおりです。
+-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 10 | warehouse #2 | | dryer | 20 | warehouse #1 | | refrigerator | 25 | warehouse #2 | | top load washer | 30 | warehouse #1 | +-----------------+----------+--------------+
クエリを実行した後の NewArrivals テーブルは次のとおりです。
+-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | microwave | 10 | warehouse #1 | | dryer | 30 | warehouse #1 | | oven | 20 | warehouse #2 | +-----------------+----------+--------------+
ご不明な点がありましたら、Google の