A linguagem de manipulação de dados (DML) do BigQuery permite atualizar, inserir e excluir dados das tabelas do BigQuery.
Para mais informações sobre como utilizar instruções DML, consulte Linguagem de manipulação de dados.
Tabelas usadas nos exemplos
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"}
]}
]}
]
Instrução INSERT
Use a instrução INSERT
para adicionar novas linhas à tabela.
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
Nas instruções INSERT
, siga estas regras:
- Os nomes das colunas são opcionais quando a tabela de destino não é particionada por tempo de ingestão.
- Não são permitidos nomes duplicados na lista de colunas de destino.
- Os valores precisam ser adicionados na mesma ordem das colunas especificadas.
- O número de valores adicionados precisa corresponder ao de colunas especificadas.
- O tipo dos valores precisa ser compatível com a coluna de destino.
Omitindo nomes de coluna
Quando os nomes das colunas são omitidos, todas as colunas na tabela de destino são incluídas em ordem crescente com base nas respectivas posições ordinais. Observe que, se a tabela de destino for particionada por tempo de ingestão, será preciso especificar os nomes das colunas.
Compatibilidade de tipo de valor
Os valores adicionados com uma instrução INSERT
precisam ser compatíveis com o tipo da coluna de destino. O tipo do valor é considerado compatível com o tipo da coluna de destino se um dos seguintes critérios for atendido:
- Esse tipo corresponde exatamente ao tipo da coluna. Por exemplo, quando o valor do tipo INT64 é inserido em uma coluna com o mesmo tipo.
- É possível forçar implicitamente o tipo de valor para outro tipo.
Exemplos de INSERT
INSERT
usando valores explícitos
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 | +-----------------+----------+--------------+
Instrução 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 | +--------------+-------+
Também é possível usar WITH
ao usar INSERT SELECT
. Por exemplo, é possível reescrever a consulta anterior usando 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)
Veja no exemplo a seguir como copiar o conteúdo de uma tabela para outra:
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
com subconsulta
Veja no exemplo a seguir como inserir uma linha em uma tabela em que um dos valores é computado usando uma subconsulta:
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
sem nomes de coluna
INSERT dataset.Warehouse VALUES('warehouse #4', 'WA'), ('warehouse #5', 'NY')
Esta é a tabela Warehouse
antes da execução da consulta:
+--------------+-------+ | warehouse | state | +--------------+-------+ | warehouse #1 | WA | | warehouse #2 | CA | | warehouse #3 | WA | +--------------+-------+
Esta é a tabela Warehouse
após a execução da consulta:
+--------------+-------+ | warehouse | state | +--------------+-------+ | warehouse #1 | WA | | warehouse #2 | CA | | warehouse #3 | WA | | warehouse #4 | WA | | warehouse #5 | NY | +--------------+-------+
INSERT
com tipos STRUCT
Veja no exemplo a seguir como inserir uma linha em uma tabela em que alguns dos campos são tipos 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)))
Esta é a tabela DetailedInventory
após a execução da consulta:
+-------------------+----------+--------------------+-------------------------------------------------+----------------------------------------------------------------------------------------------------+ | 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"}} | +-------------------+----------+--------------------+-------------------------------------------------+----------------------------------------------------------------------------------------------------+
Instrução DELETE
Use a instrução DELETE
para excluir linhas de uma tabela.
DELETE [FROM] target_name [alias]
WHERE condition
Palavra-chave WHERE
Sempre que você cria uma instrução DELETE
, é necessário usar a palavra-chave WHERE
, seguida de uma condição.
A palavra-chave WHERE
é obrigatória para qualquer instrução DELETE
.
Para excluir todas as linhas de uma tabela, defina a condição da palavra-chave WHERE
como true
:
DELETE FROM dataset.DetailedInventory WHERE true
Exemplos de DELETE
DELETE
com cláusula WHERE
DELETE dataset.Inventory
WHERE quantity = 0
Antes:
+-------------------+----------+--------------------+ | 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 | +-------------------+----------+--------------------+
Depois:
+-------------------+----------+--------------------+ | 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
com subconsulta
DELETE dataset.Inventory i
WHERE i.product NOT IN (SELECT product from dataset.NewArrivals)
Antes:
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 | +-----------------+----------+--------------+
Depois:
Inventory +-----------------+----------+--------------------+ | product | quantity | supply_constrained | +-----------------+----------+--------------------+ | dryer | 30 | NULL | | oven | 5 | NULL | | top load washer | 10 | NULL | +-----------------+----------+--------------------+
Como alternativa, use DELETE
com a cláusula EXISTS
:
DELETE dataset.Inventory
WHERE NOT EXISTS
(SELECT * from dataset.NewArrivals
WHERE Inventory.product = NewArrivals.product)
Instrução TRUNCATE TABLE
A instrução TRUNCATE TABLE
remove todas as linhas de uma tabela, mas deixa os
metadados da tabela intactos, incluindo o esquema, a descrição e os rótulos da tabela.
TRUNCATE TABLE [[project_name.]dataset_name.]table_name
Em que:
project_name
é o nome do projeto que contém a tabela. O padrão é o projeto que executa essa consulta DDL.dataset_name
é o nome do conjunto de dados que contém a tabela;table_name
é o nome da tabela a ser truncada.
Não há compatibilidade com truncamento de visualizações, visualizações materializadas, modelos ou
tabelas externas. As cotas e os limites para consultas se aplicam a instruções TRUNCATE TABLE
.
Para mais informações, consulte Cotas e limites.
Para tabelas particionadas, se a tabela exigir um filtro de partição, a instrução TRUNCATE TABLE
falhará. Para truncar a tabela, atualize antes para remover o requisito de filtro de partição e execute a instrução TRUNCATE TABLE
. Para mais informações, consulte Como atualizar requisitos de filtro de partição.
TRUNCATE TABLE
exemplo
No exemplo a seguir, removemos todas as linhas da tabela denominada Inventory
.
TRUNCATE TABLE dataset.Inventory
Instrução UPDATE
Use a instrução UPDATE
para atualizar as linhas atuais dentro de uma tabela:
UPDATE target_name [alias]
SET update_item [, update_item]*
[FROM from_clause]
WHERE condition
update_item ::= column_name = expression
Em que:
target_name
é o nome de uma tabela para atualizar;update_item
é o nome da coluna a ser atualizada e uma expressão a ser avaliada para o valor atualizado.
Se a coluna for do tipo STRUCT
, o column_name
poderá referenciar um campo em
STRUCT
usando a notação de ponto. Por exemplo, struct1.field1
.
Palavra-chave WHERE
Todas as instruções UPDATE
precisam incluir a palavra-chave WHERE
seguida de uma condição.
Para atualizar todas as linhas da tabela, use WHERE true
.
Palavra-chave FROM
Use FROM
para especificar as linhas a serem atualizadas. Também é possível usar as colunas de tabelas mescladas nas expressões SET
.
Se uma linha na tabela a ser atualizada estiver mesclada com zero linhas da cláusula FROM
, essa linha não será atualizada.
Se uma linha na tabela a ser atualizada estiver mesclada exatamente com uma linha da cláusula FROM
, essa linha será atualizada.
Se uma linha na tabela a ser atualizada estiver mesclada com mais de uma linha da cláusula FROM
, a consulta gerará o seguinte erro de ambiente de execução: UPDATE/MERGE must
match at most one source row for each target row.
.
Para especificar o predicado da mesclagem entre a tabela a ser atualizada e as tabelas na cláusula FROM
, use a cláusula WHERE
. Para ver um exemplo, consulte UPDATE
usando mesclagens.
Exemplos de UPDATE
UPDATE
com cláusula WHERE
O exemplo a seguir atualiza uma tabela chamada Inventory
reduzindo o valor do campo quantity
por 10 para todos os produtos que contêm a string washer
:
UPDATE dataset.Inventory
SET quantity = quantity - 10
WHERE product like '%washer%'
Antes:
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 | +-------------------+----------+--------------------+
Depois:
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
usando mesclagens
No exemplo a seguir:
- Gera uma tabela com totais de inventário que incluem o inventário existente e o inventário da tabela
NewArrivals
e - Marca
supply_constrained
comofalse
:
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)
Como alternativa, é possível mesclar as tabelas:
UPDATE dataset.Inventory i
SET quantity = i.quantity + n.quantity,
supply_constrained = false
FROM dataset.NewArrivals n
WHERE i.product = n.product
Antes:
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 | +-----------------+----------+--------------+
Depois:
+-------------------+----------+--------------------+ | 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
de campos aninhados
No exemplo a seguir, os campos de registro aninhados são atualizados.
UPDATE dataset.DetailedInventory
SET specifications.color = 'white',
specifications.warranty = '1 year'
WHERE product like '%washer%'
Como alternativa, atualize todo o registro:
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
de registros repetidos
No exemplo a seguir, uma entrada é anexada a um registro repetido na coluna comments
dos produtos que contêm a string washer
:
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 | +----------------------+----------+--------------------+----------------------------------------------------+----------------+
Como alternativa, use a função 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%'
No exemplo a seguir, uma segunda entrada é anexada ao registro repetido na coluna comments
para todas as linhas:
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"}'] | +----------------------+------------------------------------------------------------------------------------------------------+
Para excluir entradas de valores repetidos, use 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 | +----------------------+----------+--------------------+----------------------------------------------------+----------------+
Instrução UPDATE
usando mesclagem entre três tabelas
No exemplo a seguir, definimos supply_constrained
para true
em todos os produtos de NewArrivals
com local do armazém no estado 'WA'
.
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'
Observe que o predicado da mesclagem com a tabela atualizada (DetailedInventory
) precisa ser especificado com WHERE
. No entanto, as mesclagens entre as outras tabelas (NewArrivals
e Warehouse
) podem ser especificadas com uma cláusula JOIN ... ON
explícita. Por exemplo, a consulta a seguir é equivalente à consulta anterior:
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'
Antes:
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 | +--------------+-------+
Depois:
+----------------------+----------+--------------------+----------+----------------+ | 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 | +----------------------+----------+--------------------+----------+----------------+
Instrução MERGE
MERGE
é uma instrução DML que pode combinar operações INSERT
, UPDATE
e DELETE
em uma única instrução e executar as operações atomicamente.
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
Em que:
target_name
target_name
é o nome da tabela que você está alterando.
source_name
source_name
é um nome de tabela ou subconsulta.
merge_condition
Uma instrução
MERGE
executa umJOIN
entre o destino e a origem. Dependendo do status da correspondência (linha correspondida, somente na tabela de origem, somente na tabela de destino), a cláusulaWHEN
correspondente é, então, executada. Omerge_condition
é usado peloJOIN
para corresponder as linhas entre as tabelas de origem e de destino. Dependendo da combinação de cláusulasWHEN
, diferentes tipos deINNER
eOUTER
JOIN
serão aplicados.Se merge_condition for
FALSE
, o otimizador de consulta evitará usar umJOIN
. Essa otimização é referida como um predicado falso constante. Um predicado falso constante é útil quando você executa umDELETE
atômico no destino mais umINSERT
de uma origem (DELETE
comINSERT
também é conhecido como uma operaçãoREPLACE
).
when_clause
O
when_clause
tem três opções:MATCHED
,NOT MATCHED BY TARGET
eNOT MATCHED BY SOURCE
. É necessário haver pelo menos umwhen_clause
em cada instruçãoMERGE
.Todo
when_clause
pode ter umsearch_condition
opcional. Owhen_clause
será executado para uma linha se tantomerge_condition
esearch_condition
forem atendidos. Quando houver várias cláusulas qualificadas, apenas a primeirawhen_clause
será executada para uma linha.
matched_clause
O
matched_clause
define como atualizar ou excluir uma linha na tabela de destino, se essa linha corresponder a uma linha na tabela de origem.Se houver pelo menos um
matched_clause
executando uma operaçãoUPDATE
, um erro de ambiente de execução será retornado quando várias linhas da tabela de origem corresponderem a uma linha da tabela de destino e você estiver tentando atualizar ou excluir essa linha na tabela de destino.
not_matched_by_target_clause
- O
not_matched_by_target_clause
define como inserir na tabela de destino se uma linha da tabela de origem não corresponder a nenhuma linha na tabela de destino.
not_matched_by_source_clause
- O
not_matched_by_source_clause
define como atualizar ou excluir uma linha na tabela de destino se essa linha não corresponder a nenhuma linha na tabela de origem.
Omitindo nomes de coluna
- Em
not_matched_by_target_clause
, quando os nomes das colunas da tabela de destino são omitidos, todas as colunas na tabela de destino são incluídas em ordem crescente com base em suas posições ordinais. Observe que, se a tabela de destino for particionada por tempo de ingestão, será preciso especificar os nomes das colunas. - Em
not_matched_by_target_clause
, é possível usarROW
para incluir todas as colunas da origem na sequência crescente de suas posições ordinais. Observe que nenhuma das pseudocolunas da origem está incluída. Por exemplo, a pseudocoluna_PARTITIONTIME
não será incluída quando a origem for uma tabela particionada por tempo de ingestão.
Exemplos de MERGE
Exemplo 1
No exemplo a seguir, a consulta adicionará novos itens da tabela Inventory
à tabela DetailedInventory
. Para itens com estoque baixo, o valor supply_constrained
é definido como true
, e os comentários são adicionados.
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)
Estas são as tabelas antes de você executar a consulta:
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 | +----------------------+----------+--------------------+----------+----------------+
Esta é a tabela DetailedInventory
após a execução da consulta:
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 | +----------------------+----------+--------------------+-------------------------------------------------+----------------+
Exemplo 2
No exemplo a seguir, a consulta mescla itens da tabela NewArrivals
com a tabela Inventory
. Se um item já estiver presente em Inventory
, a
consulta incrementará o campo quantity
. Caso contrário, a consulta insere uma nova linha.
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)
Estas são as tabelas antes de você executar a consulta:
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 | +-------------------+----------+
Esta é a tabela Inventory
após a execução da consulta:
Inventory +-------------------+----------+ | product | quantity | +-------------------+----------+ | dishwasher | 30 | | dryer | 50 | | front load washer | 20 | | microwave | 20 | | oven | 35 | | refrigerator | 25 | | top load washer | 20 | +-------------------+----------+
Exemplo 3
No exemplo a seguir, a consulta aumentará a quantidade de produtos do armazém nº 1 em 20 na tabela NewArrivals
. A consulta exclui todos os outros produtos, exceto os do armazém nº 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
Esta é a tabela NewArrivals
antes da execução da consulta:
NewArrivals +-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 20 | warehouse #2 | | oven | 30 | warehouse #3 | | refrigerator | 25 | warehouse #2 | | top load washer | 10 | warehouse #1 | +-----------------+----------+--------------+
Esta é a tabela NewArrivals
após a execução da consulta:
NewArrivals +-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 20 | warehouse #2 | | refrigerator | 25 | warehouse #2 | | top load washer | 30 | warehouse #1 | +-----------------+----------+--------------+
Exemplo 4
No exemplo a seguir, a consulta substituirá todos os produtos, como '%washer%'
na tabela Inventory
, usando os valores na tabela NewArrivals
.
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
Estas são as tabelas antes de você executar a consulta:
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 | +-------------------+----------+
Esta é a tabela Inventory
após a execução da consulta:
Inventory +-----------------+----------+ | product | quantity | +-----------------+----------+ | dryer | 50 | | microwave | 20 | | oven | 35 | | refrigerator | 25 | | top load washer | 30 | +-----------------+----------+
Exemplo 5
No exemplo a seguir, a consulta adicionará um comentário na tabela DetailedInventory
se o produto tiver uma quantidade menor que a média na tabela Inventory
.
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')])
Estas são as tabelas antes de você executar a consulta:
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 | +----------------------+----------+--------------------+-------------------------------------------------+----------------+
Esta é a tabela DetailedInventory
após a execução da consulta:
+----------------------+----------+--------------------+-----------------------------------------------------------------------------------------------+----------------+ | 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 | +----------------------+----------+--------------------+-----------------------------------------------------------------------------------------------+----------------+
Exemplo 6
No exemplo a seguir, a consulta aumentará o estoque de produtos do armazém em CA
. Os produtos de outros estados serão excluídos e qualquer produto que não esteja presente na tabela NewArrivals
permanecerá inalterado.
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
Estas são as tabelas antes de você executar a consulta:
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 | +-----------------+----------+
Esta é a tabela Inventory
após a execução da consulta:
Inventory +--------------+----------+ | product | quantity | +--------------+----------+ | dryer | 70 | | microwave | 20 | | oven | 35 | | refrigerator | 50 | +--------------+----------+
Exemplo 7
No exemplo a seguir, um erro de ambiente de execução é retornado porque a consulta tenta atualizar uma tabela de destino quando a origem contém mais de uma linha correspondente. Para resolver o erro, é preciso alterar merge_condition
ou search_condition
para evitar correspondências duplicadas na origem.
MERGE dataset.Inventory T
USING dataset.NewArrivals S
ON T.product = S.product
WHEN MATCHED THEN
UPDATE SET quantity = T.quantity + S.quantity
Estas são as tabelas antes de você executar a consulta:
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 | +--------------+----------+
Quando você executa a consulta, o seguinte erro é retornado:
UPDATE/MERGE must match at most one source row for each target row
Exemplo 8
No exemplo a seguir, todos os produtos na tabela NewArrivals
serão substituídos por valores da subconsulta. A cláusula INSERT
não especifica nomes de colunas para a tabela de destino ou para a subconsulta de origem.
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
Esta é a tabela NewArrivals
antes da execução da consulta:
+-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 10 | warehouse #2 | | dryer | 20 | warehouse #1 | | refrigerator | 25 | warehouse #2 | | top load washer | 30 | warehouse #1 | +-----------------+----------+--------------+
Esta é a tabela NewArrivals
após a execução da consulta:
+-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | microwave | 10 | warehouse #1 | | dryer | 30 | warehouse #1 | | oven | 20 | warehouse #2 | +-----------------+----------+--------------+