Com a linguagem de manipulação de dados (DML, na sigla em inglês) do BigQuery, é possível 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 essas regras:
- Os nomes das colunas precisam ser especificados.
- 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.
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 a coluna de destino se um dos seguintes critérios é 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.
- Esse tipo pode ser implicitamente convertido 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 com INSERT SELECT. Veja, por exemplo, a consulta anterior reescrita com 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 na qual 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 | +----------------------+----------+--------------------+----------+----------------+
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, é obrigató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 UPDATE
Use a instrução UPDATE para atualizar as linhas existentes dentro de uma tabela:
UPDATE target_name [alias]
SET update_item [, update_item]*
[FROM from_clause]
WHERE condition
update_item ::= path_expression = expression
Em que:
target_nameé o nome da tabela a ser atualizada;update_itemcontém um nome de tabela e uma expressão de atualização.
Palavra-chave WHERE
Todas as instruções UPDATE precisam incluir a palavra-chave WHERE seguida de uma condição.
Para atualizar todas as linhas na 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 associadas nas expressões SET.
Se uma linha na tabela a ser atualizada está associada a 0 linha da cláusula FROM, essa linha não é atualizada.
Se uma linha na tabela a ser atualizada é associada a exatamente 1 linha da cláusula FROM, essa linha é atualizada.
Se uma linha na tabela a ser atualizada é associada com mais de uma linha da cláusula FROM, um erro de execução é gerado na consulta.
Para especificar o predicado da junção 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 joins.
Exemplos de UPDATE
UPDATE com cláusula WHERE
No exemplo a seguir, uma tabela chamada Inventory é atualizada por meio da redução do valor do campo quantity em 10 unidades 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 joins
No exemplo a seguir:
- Uma tabela é gerada com totais de inventário que incluem o inventário existente e o inventário da tabela
NewArrivals. - O campo
supply_constrainedé marcado 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, associe 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 para 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 junção entre três tabelas
No exemplo a seguir, supply_constrained é definido como verdadeiro para todos os produtos da tabela NewArrivals onde o local do depósito é 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'
Note que o predicado da junção com a tabela atualizada (DetailedInventory) precisa ser especificado com WHERE. No entanto, as junções entre as outras tabelas (NewArrivals e Warehouse) podem ser especificadas usando 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
Uma instrução 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_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 ])
expr ::= expression | DEFAULT
Em que:
-
target_nametarget_nameé o nome da tabela que você está alterando.
-
source_namesource_nameé um nome de tabela ou subconsulta.
-
merge_condition- Uma instrução
MERGEexecuta umJOINentre 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áusulaWHENcorrespondente é, então, executada. Amerge_conditioné usada peloJOINpara corresponder as linhas entre as tabelas de origem e de destino. Dependendo da combinação de cláusulasWHEN, diferentes tipos deINNEReOUTERJOINsão aplicados. - Se a 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 umDELETEatômico no destino mais umINSERTde uma origem.DELETEcomINSERTtambém é conhecido como uma operaçãoREPLACE.
-
when_clause- O
when_clausetem três opções:MATCHED,NOT MATCHED BY TARGETENOT MATCHED BY SOURCE. É preciso haver pelo menos umwhen_clauseem cada instruçãoMERGE. - Cada
when_clausepode ter umasearch_conditionopcional. Awhen_clauseé executada para uma linha se tanto amerge_conditionquanto asearch_conditionforem satisfeitas. Quando há várias cláusulas qualificadas, apenas a primeirawhen_clauseé executada para uma linha.
-
matched_clause- A
matched_clausedefine 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 uma
matched_clauseexecutando uma operaçãoUPDATE, um erro de tempo 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- A
not_matched_by_target_clausedefine 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- A
not_matched_by_source_clausedefine como atualizar ou excluir uma linha na tabela de destino, se essa linha não corresponder a nenhuma linha na tabela de origem.
Exemplos de MERGE
Exemplo 1
No exemplo a seguir, a consulta adiciona 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 de DetailedInventory depois de você executar a 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 adiciona novos itens à tabela Inventory e a quantidade de produtos existentes na tabela NewArrivals é atualizada.
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 de Inventory depois de você executar a 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 aumenta a quantidade de produtos do armazém nº 1 por 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 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 | +-----------------+----------+--------------+
Esta é a tabela de NewArrivals depois de você executar a 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 substitui todos os produtos, como '%washer%' na tabela Inventory, usando os valores da 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 de Inventory depois de você executar a 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 de DetailedInventory depois de você executar a 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 aumenta o estoque de produtos do depósito na CA. Os produtos de outros estados são excluídos e qualquer produto que não esteja presente na tabela NewArrivals permanece 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 de Inventory depois de você executar a consulta:
Inventory +--------------+----------+ | product | quantity | +--------------+----------+ | dryer | 70 | | microwave | 20 | | oven | 35 | | refrigerator | 50 | +--------------+----------+
Exemplo 7
No exemplo a seguir, um erro de tempo de execução é retornado porque a consulta tenta atualizar uma tabela de destino quando a origem contém mais de uma linha correspondente.
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 | +--------------+----------+
Precisa de ajuda? Acesse nossa