Google 標準 SQL のデータ操作言語(DML)ステートメント
BigQuery のデータ操作言語(DML)を使用して、BigQuery テーブルのデータを更新、挿入、削除できます。
DML ステートメントの使用方法について詳しくは、データ操作言語の使用をご覧ください。
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 | DEFAULT
INSERT
ステートメントは、以下の規則に従う必要があります。
- 抽出先テーブルが取り込み時間パーティション分割テーブルではない場合、列名は省略可能です。
- 抽出先の列のリストでは同じ名前を使用できません。
- 指定した列と同じ順序で値を追加します。
- 追加した数値は、指定した列の数と一致しなければなりません。
- 数値は、抽出先の列と互換性のあるタイプにします。
- 値式が
DEFAULT
の場合、列のデフォルト値が使用されます。列にデフォルト値がない場合、値はデフォルトのNULL
になります。
列名を省略する
列名を省略すると、抽出先テーブルにあるすべての列は順序位置に基づいて昇順で挿入されます。省略された列にデフォルト値がある場合は、その値が使用されます。それ以外の場合、列の値は NULL
です。抽出先テーブルが取り込み時間パーティション分割テーブルの場合は、列名が指定されている必要があります。
数値タイプの互換性
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 | +-------------------+----------+--------------------+
列のデフォルト値を設定する場合は、値の代わりに DEFAULT
キーワードを使用してデフォルト値を挿入できます。
ALTER TABLE dataset.NewArrivals ALTER COLUMN quantity SET DEFAULT 100;
INSERT dataset.NewArrivals (product, quantity, warehouse)
VALUES('top load washer', DEFAULT, 'warehouse #1'),
('dryer', 200, 'warehouse #2'),
('oven', 300, 'warehouse #3');
+-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 200 | warehouse #2 | | oven | 300 | warehouse #3 | | top load washer | 100 | warehouse #1 | +-----------------+----------+--------------+
INSERT SELECT
ステートメント
INSERT dataset.Warehouse (warehouse, state)
SELECT *
FROM UNNEST([('warehouse #1', 'WA'),
('warehouse #2', 'CA'),
('warehouse #3', 'WA')])
+--------------+-------+ | warehouse | state | +--------------+-------+ | warehouse #1 | WA | | warehouse #2 | CA | | warehouse #3 | WA | +--------------+-------+
INSERT SELECT
を使用するときにも WITH
を使用できます。たとえば、次のようにして WITH
を使用して前のクエリを書き換えることができます。
INSERT dataset.Warehouse (warehouse, state)
WITH w AS (
SELECT ARRAY<STRUCT<warehouse string, state string>>
[('warehouse #1', 'WA'),
('warehouse #2', 'CA'),
('warehouse #3', 'WA')] col
)
SELECT warehouse, state FROM w, UNNEST(w.col)
下の例は、テーブルの内容を別のテーブルにコピーする方法を示しています。
INSERT dataset.DetailedInventory (product, quantity, supply_constrained)
SELECT product, quantity, false
FROM dataset.Inventory
+----------------------+----------+--------------------+----------+----------------+ | product | quantity | supply_constrained | comments | specifications | +----------------------+----------+--------------------+----------+----------------+ | dishwasher | 30 | false | [] | NULL | | dryer | 30 | false | [] | NULL | | front load washer | 20 | false | [] | NULL | | microwave | 20 | false | [] | NULL | | oven | 5 | false | [] | NULL | | refrigerator | 10 | false | [] | NULL | | top load washer | 10 | false | [] | NULL | +----------------------+----------+--------------------+----------+----------------+
サブクエリを使用する INSERT VALUES
次の例はテーブルに行を挿入する方法を示しています。サブクエリにより、1 つの数値が計算されています。
INSERT dataset.DetailedInventory (product, quantity)
VALUES('countertop microwave',
(SELECT quantity FROM dataset.DetailedInventory
WHERE product = 'microwave'))
+----------------------+----------+--------------------+----------+----------------+ | product | quantity | supply_constrained | comments | specifications | +----------------------+----------+--------------------+----------+----------------+ | countertop microwave | 20 | NULL | [] | NULL | | dishwasher | 30 | false | [] | NULL | | dryer | 30 | false | [] | NULL | | front load washer | 20 | false | [] | NULL | | microwave | 20 | false | [] | NULL | | oven | 5 | false | [] | NULL | | refrigerator | 10 | false | [] | NULL | | top load washer | 10 | false | [] | NULL | +----------------------+----------+--------------------+----------+----------------+
列名を指定しない INSERT
INSERT dataset.Warehouse VALUES('warehouse #4', 'WA'), ('warehouse #5', 'NY')
クエリを実行する前の Warehouse
テーブルは次のとおりです。
+--------------+-------+ | warehouse | state | +--------------+-------+ | warehouse #1 | WA | | warehouse #2 | CA | | warehouse #3 | WA | +--------------+-------+
クエリを実行した後の Warehouse
テーブルは次のとおりです。
+--------------+-------+ | warehouse | state | +--------------+-------+ | warehouse #1 | WA | | warehouse #2 | CA | | warehouse #3 | WA | | warehouse #4 | WA | | warehouse #5 | NY | +--------------+-------+
STRUCT
型の INSERT
次の例は、テーブルに行を挿入する方法を示しています。ここで、一部のフィールドは STRUCT
型です。
INSERT dataset.DetailedInventory
VALUES('top load washer', 10, FALSE, [(CURRENT_DATE, "comment1")], ("white","1 year",(30,40,28))),
('front load washer', 20, FALSE, [(CURRENT_DATE, "comment1")], ("beige","1 year",(35,45,30)))
クエリを実行した後の DetailedInventory
テーブルは次のとおりです。
+-------------------+----------+--------------------+-------------------------------------------------+----------------------------------------------------------------------------------------------------+ | product | quantity | supply_constrained | comments | specifications | +-------------------+----------+--------------------+-------------------------------------------------+----------------------------------------------------------------------------------------------------+ | front load washer | 20 | false | [{"created":"2021-02-09","comment":"comment1"}] | {"color":"beige","warranty":"1 year","dimensions":{"depth":"35.0","height":"45.0","width":"30.0"}} | | top load washer | 10 | false | [{"created":"2021-02-09","comment":"comment1"}] | {"color":"white","warranty":"1 year","dimensions":{"depth":"30.0","height":"40.0","width":"28.0"}} | +-------------------+----------+--------------------+-------------------------------------------------+----------------------------------------------------------------------------------------------------+
ARRAY
型の INSERT
次の例は、フィールドの 1 つが ARRAY
型のテーブルに行を挿入する方法を示しています。
CREATE TABLE IF NOT EXISTS dataset.table1 (names ARRAY<STRING>);
INSERT INTO dataset.table1 (names)
VALUES (["name1","name2"])
クエリを実行した後のテーブルは次のとおりです。
+-------------------+
| names |
+-------------------+
| ["name1","name2"] |
+-------------------+
DELETE
ステートメント
テーブルから行を削除する場合は、DELETE
ステートメントを使用します。
DELETE [FROM] target_name [alias]
WHERE condition
テーブル内の行をすべて削除するには、TRUNCATE TABLE ステートメントを使用します。
WHERE
キーワード
DELETE
ステートメントを作成するごとに、WHERE
キーワードと条件を指定する必要があります。
WHERE
キーワードは、すべての DELETE
ステートメントに必須です。
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
ステートメントは失敗します。テーブルを切り詰めるには、まずテーブルを更新してパーティション フィルタの要件を削除し、TRUNCATE TABLE
ステートメントを実行します。詳細については、パーティション フィルタの要件の更新をご覧ください。
TRUNCATE TABLE
の例
次の例では、Inventory
という名前のテーブルからすべての行を削除しています。
TRUNCATE TABLE dataset.Inventory
UPDATE
ステートメント
テーブルの既存の行を更新する場合は、UPDATE
ステートメントを使用します。
UPDATE target_name [[AS] alias]
SET set_clause
[FROM from_clause]
WHERE condition
set_clause ::= update_item[, ...]
update_item ::= column_name = expression
ここで
target_name
は、更新するテーブルの名前です。update_item
は、更新する列の名前と更新後の値を評価する式です。 式にDEFAULT
キーワードを含めることができます。これは、その列のデフォルト値に置き換えられます。
列が STRUCT
型の場合、column_name
はドット表記を使用して STRUCT
内のフィールドを参照できます。例: struct1.field1
WHERE
キーワード
各 UPDATE
ステートメントには必ず、WHERE
キーワードと条件文を含める必要があります。
テーブル内のすべての行を更新するには、WHERE true
とします。
FROM
キーワード
必要に応じて、UPDATE
ステートメントには、FROM
句を含めることができます。
FROM
句を使用して、ターゲット テーブルで更新する行を指定できます。結合テーブルの列を SET
句または WHERE
条件で使用することもできます。
WHERE
句に条件が指定されていない場合、FROM
句の結合はクロス結合になります。条件が指定されている場合は、内部結合になります。どちらの場合も、ターゲット テーブルの行は FROM
句の最大で 1 行と結合できます。
更新する対象のテーブルと FROM
句のテーブルの間に結合属性を指定するには、WHERE
句を使用します。例については、結合を使用する UPDATE
をご覧ください。
注意点
SET
句はターゲット テーブルの列と、FROM
句の任意のFROM
項目から列を参照できます。名前が競合している場合、非修飾参照は曖昧として扱われます。- ターゲット テーブルがテーブル名として
FROM
句にある場合、自己結合を行うにはターゲット テーブルにエイリアスが必要です。 - 更新する対象のテーブルの行が
FROM
句の行と 1 行も結合しない場合、その行は更新されません。 - 更新する対象のテーブルの行が
FROM
句の 1 つの行にのみ結合する場合、その行は更新されます。 - 更新する対象のテーブルの行が
FROM
句の複数の行と結合する場合、クエリはUPDATE/MERGE must match at most one source row for each target row.
のランタイム エラーを生成します。
UPDATE
の例
WHERE
句を含む UPDATE
次の例は、Inventory
という名前のテーブルに対して、文字列 washer
を含むすべての商品の quantity
フィールドの値を 10 だけ減らすという更新を実行します。supply_constrained
列のデフォルト値が TRUE
に設定されているとします。
UPDATE dataset.Inventory
SET quantity = quantity - 10,
supply_constrained = DEFAULT
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 | true | | dryer | 30 | NULL | | front load washer | 10 | true | | microwave | 20 | NULL | | oven | 5 | NULL | | refrigerator | 10 | NULL | | top load washer | 0 | true | +-------------------+----------+--------------------+
結合を使用する UPDATE
次の例では、既存の在庫と NewArrivals
テーブルの在庫を含む在庫合計のテーブルを生成し、supply_constrained
を false
とマークします。
UPDATE dataset.Inventory
SET quantity = quantity +
(SELECT quantity FROM dataset.NewArrivals
WHERE Inventory.product = NewArrivals.product),
supply_constrained = false
WHERE product IN (SELECT product FROM dataset.NewArrivals)
以下のようにテーブルを結合することもできます。
UPDATE dataset.Inventory i
SET quantity = i.quantity + n.quantity,
supply_constrained = false
FROM dataset.NewArrivals n
WHERE i.product = n.product
使用前:
Inventory +-------------------+----------+--------------------+ | product | quantity | supply_constrained | +-------------------+----------+--------------------+ | dishwasher | 30 | NULL | | dryer | 30 | NULL | | front load washer | 20 | NULL | | microwave | 20 | NULL | | oven | 5 | NULL | | refrigerator | 10 | NULL | | top load washer | 10 | NULL | +-------------------+----------+--------------------+ NewArrivals +-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 200 | warehouse #2 | | oven | 300 | warehouse #3 | | top load washer | 100 | warehouse #1 | +-----------------+----------+--------------+
使用後:
+-------------------+----------+--------------------+ | product | quantity | supply_constrained | +-------------------+----------+--------------------+ | dishwasher | 30 | NULL | | dryer | 230 | false | | front load washer | 20 | NULL | | microwave | 20 | NULL | | oven | 305 | false | | refrigerator | 10 | NULL | | top load washer | 110 | false | +-------------------+----------+--------------------+
ネストされたフィールドに対する UPDATE
次の例は、ネストされたレコード フィールドの更新を示します。
UPDATE dataset.DetailedInventory
SET specifications.color = 'white',
specifications.warranty = '1 year'
WHERE product like '%washer%'
次のように、レコード全体を更新することもできます。
UPDATE dataset.DetailedInventory
SET specifications
= STRUCT<color STRING, warranty STRING,
dimensions STRUCT<depth FLOAT64, height FLOAT64, width FLOAT64>>('white', '1 year', NULL)
WHERE product like '%washer%'
+----------------------+----------+--------------------+----------+---------------------------------------------------------+ | product | quantity | supply_constrained | comments | specifications | +----------------------+----------+--------------------+----------+---------------------------------------------------------+ | countertop microwave | 20 | NULL | [] | NULL | | dishwasher | 30 | false | [] | {"color":"white","warranty":"1 year","dimensions":null} | | dryer | 30 | false | [] | NULL | | front load washer | 20 | false | [] | {"color":"white","warranty":"1 year","dimensions":null} | | microwave | 20 | false | [] | NULL | | oven | 5 | false | [] | NULL | | refrigerator | 10 | false | [] | NULL | | top load washer | 10 | false | [] | {"color":"white","warranty":"1 year","dimensions":null} | +----------------------+----------+--------------------+----------+---------------------------------------------------------+
繰り返しレコードに対する UPDATE
次の例は、文字列 washer
を含む商品の comments
列の繰り返しレコードにエントリを追加します。
UPDATE dataset.DetailedInventory
SET comments = ARRAY(
SELECT comment FROM UNNEST(comments) AS comment
UNION ALL
SELECT (CAST('2016-01-01' AS DATE), 'comment1')
)
WHERE product like '%washer%'
+----------------------+----------+--------------------+----------------------------------------------------+----------------+ | product | quantity | supply_constrained | comments | specifications | +----------------------+----------+--------------------+----------------------------------------------------+----------------+ | countertop microwave | 20 | NULL | [] | NULL | | dishwasher | 30 | false | [u'{"created":"2016-01-01","comment":"comment1"}'] | NULL | | dryer | 30 | false | [] | NULL | | front load washer | 20 | false | [u'{"created":"2016-01-01","comment":"comment1"}'] | NULL | | microwave | 20 | false | [] | NULL | | oven | 5 | false | [] | NULL | | refrigerator | 10 | false | [] | NULL | | top load washer | 10 | false | [u'{"created":"2016-01-01","comment":"comment1"}'] | NULL | +----------------------+----------+--------------------+----------------------------------------------------+----------------+
ARRAY_CONCAT
関数を使用することもできます。
UPDATE dataset.DetailedInventory
SET comments = ARRAY_CONCAT(comments,
ARRAY<STRUCT<created DATE, comment STRING>>[(CAST('2016-01-01' AS DATE), 'comment1')])
WHERE product like '%washer%'
次の例は、すべての行の comments
列の繰り返しレコードに 2 番目のエントリを追加します。
UPDATE dataset.DetailedInventory
SET comments = ARRAY(
SELECT comment FROM UNNEST(comments) AS comment
UNION ALL
SELECT (CAST('2016-01-01' AS DATE), 'comment2')
)
WHERE true
SELECT product, comments FROM dataset.DetailedInventory
+----------------------+------------------------------------------------------------------------------------------------------+ | product | comments | +----------------------+------------------------------------------------------------------------------------------------------+ | countertop microwave | [u'{"created":"2016-01-01","comment":"comment2"}'] | | dishwasher | [u'{"created":"2016-01-01","comment":"comment1"}', u'{"created":"2016-01-01","comment":"comment2"}'] | | dryer | [u'{"created":"2016-01-01","comment":"comment2"}'] | | front load washer | [u'{"created":"2016-01-01","comment":"comment1"}', u'{"created":"2016-01-01","comment":"comment2"}'] | | microwave | [u'{"created":"2016-01-01","comment":"comment2"}'] | | oven | [u'{"created":"2016-01-01","comment":"comment2"}'] | | refrigerator | [u'{"created":"2016-01-01","comment":"comment2"}'] | | top load washer | [u'{"created":"2016-01-01","comment":"comment1"}', u'{"created":"2016-01-01","comment":"comment2"}'] | +----------------------+------------------------------------------------------------------------------------------------------+
繰り返し値のエントリを削除するには、WHERE ... NOT LIKE
を使用します。
UPDATE dataset.DetailedInventory
SET comments = ARRAY(
SELECT c FROM UNNEST(comments) AS c
WHERE c.comment NOT LIKE '%comment2%'
)
WHERE true
+----------------------+----------+--------------------+----------------------------------------------------+----------------+ | product | quantity | supply_constrained | comments | specifications | +----------------------+----------+--------------------+----------------------------------------------------+----------------+ | countertop microwave | 20 | NULL | [] | NULL | | dishwasher | 30 | false | [u'{"created":"2016-01-01","comment":"comment1"}'] | NULL | | dryer | 30 | false | [] | NULL | | front load washer | 20 | false | [u'{"created":"2016-01-01","comment":"comment1"}'] | NULL | | microwave | 20 | false | [] | NULL | | oven | 5 | false | [] | NULL | | refrigerator | 10 | false | [] | NULL | | top load washer | 10 | false | [u'{"created":"2016-01-01","comment":"comment1"}'] | NULL | +----------------------+----------+--------------------+----------------------------------------------------+----------------+
3 つのテーブル間の結合を使用する UPDATE
ステートメント
次の例は、倉庫の場所が 'WA'
の状態にある NewArrivals
のすべての商品について supply_constrained
を true
に設定します。
UPDATE dataset.DetailedInventory
SET supply_constrained = true
FROM dataset.NewArrivals, dataset.Warehouse
WHERE DetailedInventory.product = NewArrivals.product AND
NewArrivals.warehouse = Warehouse.warehouse AND
Warehouse.state = 'WA'
更新されたテーブル(DetailedInventory
)に結合するための結合属性には WHERE
を使用することに注意してください。ただし、その他のテーブル(NewArrivals
と Warehouse
)の結合を指定するには、明示的に JOIN ... ON
句を使用します。たとえば、次のクエリは上記のクエリと同じことになります。
UPDATE dataset.DetailedInventory
SET supply_constrained = true
FROM dataset.NewArrivals
INNER JOIN dataset.Warehouse
ON NewArrivals.warehouse = Warehouse.warehouse
WHERE DetailedInventory.product = NewArrivals.product AND
Warehouse.state = 'WA'
使用前:
DetailedInventory +----------------------+----------+--------------------+----------+----------------+ | product | quantity | supply_constrained | comments | specifications | +----------------------+----------+--------------------+----------+----------------+ | countertop microwave | 20 | NULL | [] | NULL | | dishwasher | 30 | false | [] | NULL | | dryer | 30 | false | [] | NULL | | front load washer | 20 | false | [] | NULL | | microwave | 20 | false | [] | NULL | | oven | 5 | false | [] | NULL | | refrigerator | 10 | false | [] | NULL | | top load washer | 10 | false | [] | NULL | +----------------------+----------+--------------------+----------+----------------+ New arrivals +-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 200 | warehouse #2 | | oven | 300 | warehouse #3 | | top load washer | 100 | warehouse #1 | +-----------------+----------+--------------+ Warehouse +--------------+-------+ | warehouse | state | +--------------+-------+ | warehouse #1 | WA | | warehouse #2 | CA | | warehouse #3 | WA | +--------------+-------+
使用後:
+----------------------+----------+--------------------+----------+----------------+ | product | quantity | supply_constrained | comments | specifications | +----------------------+----------+--------------------+----------+----------------+ | countertop microwave | 20 | NULL | [] | NULL | | dishwasher | 30 | false | [] | NULL | | dryer | 30 | false | [] | NULL | | front load washer | 20 | false | [] | NULL | | microwave | 20 | false | [] | NULL | | oven | 5 | true | [] | NULL | | refrigerator | 10 | false | [] | NULL | | top load washer | 10 | true | [] | NULL | +----------------------+----------+--------------------+----------+----------------+
MERGE
ステートメント
MERGE
ステートメントは、INSERT
、UPDATE
、DELETE
オペレーションを 1 つのステートメントに結合し、オペレーションをアトミックに実行できる DML ステートメントです。
MERGE [INTO] target_name [[AS] alias]
USING source_name
ON merge_condition
{ when_clause } +
when_clause ::= matched_clause | not_matched_by_target_clause | not_matched_by_source_clause
matched_clause ::= WHEN MATCHED [ AND search_condition ] THEN { merge_update_clause | merge_delete_clause }
not_matched_by_target_clause ::= WHEN NOT MATCHED [BY TARGET] [ AND search_condition ] THEN merge_insert_clause
not_matched_by_source_clause ::= WHEN NOT MATCHED BY SOURCE [ AND search_condition ] THEN { merge_update_clause | merge_delete_clause }
merge_condition ::= bool_expression
search_condition ::= bool_expression
merge_update_clause ::= UPDATE SET update_item [, update_item]*
update_item ::= column_name = expression
merge_delete_clause ::= DELETE
merge_insert_clause ::= INSERT [(column_1 [, ..., column_n ])] input
input ::= VALUES (expr_1 [, ..., expr_n ]) | ROW
expr ::= expression | DEFAULT
ここで
target_name
target_name
は、変更するテーブルの名前です。
source_name
source_name
は、テーブル名またはサブクエリです。
merge_condition
MERGE
ステートメントは、抽出先と抽出元の間でJOIN
を実行します。次に、一致ステータス(行一致、抽出元テーブルのみ、抽出先テーブルのみ)に応じて、対応するWHEN
句が実行されます。merge_condition
は、抽出元テーブルと抽出先テーブルの間で行を照合するために、JOIN
によって使用されます。WHEN
句の組み合わせによって、異なるINNER
とOUTER
JOIN
タイプが適用されます。merge_condition が
FALSE
の場合、クエリ オプティマイザーはJOIN
の使用を回避します。この最適化は、定数 false 属性といいます。定数 false 属性は、抽出先でアトミックなDELETE
を実行するとともに、抽出元のINSERT
を実行する場合(DELETE
とINSERT
の組み合わせはREPLACE
オペレーションと呼ばれます)に利用できます。merge_condition
で使用される列の両方にNULL
値が含まれている場合は、X = Y OR (X IS NULL AND Y IS NULL)
のように指定します。これにより、結合が 2 つのNULL
値に基づいているという状況を回避できます。NULL = NULL
はTRUE
ではなくNULL
に評価され、結果で重複する行を作成します。
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
フィールドの値を増分します。それ以外の場合は、クエリによって新しい行が挿入されます。
supply_constrained
列のデフォルト値が NULL
に設定されているとします。
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 | supply_constrained | +-------------------+----------+--------------------+ | dishwasher | 30 | false | | dryer | 30 | false | | front load washer | 20 | false | | microwave | 20 | false | | oven | 5 | true | | top load washer | 10 | true | +-------------------+----------+--------------------+
クエリを実行した後の Inventory
テーブルは次のとおりです。
Inventory +-------------------+----------+--------------------+ | product | quantity | supply_constrained | +-------------------+----------+--------------------+ | dishwasher | 30 | false | | dryer | 50 | false | | front load washer | 20 | false | | microwave | 20 | false | | oven | 35 | true | | refrigerator | 25 | NULL | | top load washer | 20 | true | +-------------------+----------+--------------------+
例 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 | +-----------------+----------+--------------+
この例に使用されているテーブル
このドキュメントのクエリの例では、次の表を使用します。
インベントリ テーブル
[
{"name": "product", "type": "string"},
{"name": "quantity", "type": "integer"},
{"name": "supply_constrained", "type": "boolean"}
]
このテーブルを作成するための DDL ステートメント:
CREATE OR REPLACE TABLE
dataset.Inventory (product STRING,
quantity INT64,
supply_constrained BOOLEAN);
NewArrivals テーブル
[
{"name": "product", "type": "string"},
{"name": "quantity", "type": "integer"},
{"name": "warehouse", "type": "string"}
]
このテーブルを作成するための DDL ステートメント:
CREATE OR REPLACE TABLE
dataset.NewArrivals (product STRING,
quantity INT64,
warehouse STRING);
ウェアハウス テーブル
[
{"name": "warehouse", "type": "string"},
{"name": "state", "type": "string"}
]
このテーブルを作成するための DDL ステートメント:
CREATE OR REPLACE TABLE
dataset.Warehouse (warehouse STRING,
state 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"}
]}
]}
]
このテーブルを作成するための DDL ステートメント:
CREATE OR REPLACE TABLE
dataset.DetailedInventory (product STRING,
quantity INT64,
supply_constrained BOOLEAN,
comments ARRAY<STRUCT<created DATE, comment STRING>>,
specifications STRUCT<color STRING, warranty STRING,
dimensions STRUCT<depth FLOAT64, height FLOAT64, width FLOAT64>>);