El lenguaje de manipulación de datos de BigQuery (DML) te permite actualizar, insertar y borrar datos de tus tablas de BigQuery.
Para obtener información sobre cómo usar las Declaraciones DML, consulta Lenguaje de manipulación de datos.
Tablas usadas en ejemplos
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"}
]}
]}
]
Declaración INSERT
Usa la declaración INSERT
cuando quieras agregar filas nuevas a una tabla.
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
Las declaraciones INSERT
deben cumplir con las siguientes reglas:
- Los nombres de columna son opcionales si la tabla de destino no es una tabla particionada por tiempo de transferencia.
- No se permiten nombres duplicados en la lista de columnas de destino.
- Los valores deben agregarse en el mismo orden que las columnas especificadas.
- La cantidad de valores agregados debe coincidir con la cantidad de columnas especificadas.
- Los valores deben tener un tipo que sea compatible con la columna de destino.
Omite nombres de columna
Cuando se omiten los nombres de columna, todas las columnas de la tabla de destino se incluyen en orden ascendente en función de sus posiciones ordinales. Ten en cuenta que, si la tabla de destino es una tabla particionada por tiempo de transferencia, se deben especificar los nombres de columna.
Compatibilidad de tipo de valor
Los valores agregados con una declaración INSERT
deben ser compatibles con el tipo de columna de destino. El tipo de un valor se considera compatible con el tipo de la columna de destino si se cumple uno de los siguientes criterios:
- El tipo de valor coincide exactamente con el tipo de columna. Por ejemplo, si se inserta un valor de tipo INT64 en una columna que también tiene un tipo de INT64.
- El tipo de valor es uno que se puede forzar implícitamente a otro tipo.
Ejemplos de INSERT
INSERT
con 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 | +-----------------+----------+--------------+
Declaración 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 | +--------------+-------+
También puedes usar WITH
cuando usas INSERT SELECT
. Por ejemplo, puedes volver a escribir la consulta anterior con 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)
En el siguiente ejemplo, se muestra cómo copiar el contenido de una tabla a otra tabla:
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
con subconsulta
En el ejemplo siguiente, se muestra cómo insertar una fila en una tabla, en la que uno de los valores se calcula mediante una 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
sin nombres de columna
INSERT dataset.Warehouse VALUES('warehouse #4', 'WA'), ('warehouse #5', 'NY')
Esta es la tabla Warehouse
antes de ejecutar la consulta:
+--------------+-------+ | warehouse | state | +--------------+-------+ | warehouse #1 | WA | | warehouse #2 | CA | | warehouse #3 | WA | +--------------+-------+
Esta es la tabla Warehouse
después de ejecutar la consulta:
+--------------+-------+ | warehouse | state | +--------------+-------+ | warehouse #1 | WA | | warehouse #2 | CA | | warehouse #3 | WA | | warehouse #4 | WA | | warehouse #5 | NY | +--------------+-------+
INSERT
con tipos STRUCT
En el siguiente ejemplo, se muestra cómo insertar una fila en una tabla, en la que algunos de los campos son 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 es la tabla DetailedInventory
después de ejecutar la 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"}} | +-------------------+----------+--------------------+-------------------------------------------------+----------------------------------------------------------------------------------------------------+
Declaración DELETE
Usa la declaración DELETE
cuando quieras quitar filas de una tabla.
DELETE [FROM] target_name [alias]
WHERE condition
Palabra clave WHERE
Cada vez que construyas una declaración DELETE
, debes usar la palabra clave WHERE
, seguida de una condición.
La palabra clave WHERE
es obligatoria para cualquier declaración DELETE
.
Para borrar todas las filas de una tabla, establece la condición de palabra clave WHERE
en true
:
DELETE FROM dataset.DetailedInventory WHERE true
Ejemplos de DELETE
DELETE
con la 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 | +-------------------+----------+--------------------+
Después:
+-------------------+----------+--------------------+ | 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
con 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 | +-----------------+----------+--------------+
Después:
Inventory +-----------------+----------+--------------------+ | product | quantity | supply_constrained | +-----------------+----------+--------------------+ | dryer | 30 | NULL | | oven | 5 | NULL | | top load washer | 10 | NULL | +-----------------+----------+--------------------+
Si no, puedes usar DELETE
con la cláusula EXISTS
:
DELETE dataset.Inventory
WHERE NOT EXISTS
(SELECT * from dataset.NewArrivals
WHERE Inventory.product = NewArrivals.product)
Sentencia TRUNCATE TABLE
La sentencia TRUNCATE TABLE
quita todas las filas de una tabla, pero deja los metadatos de la tabla intactos, incluidos el esquema de la tabla, la descripción y las etiquetas.
TRUNCATE TABLE [[project_name.]dataset_name.]table_name
Aquí:
project_name
es el nombre del proyecto que contiene la tabla. La configuración predeterminada es el proyecto que ejecuta esta consulta de DDL.dataset_name
es el nombre del conjunto de datos que contiene la tabla.table_name
es el nombre de la tabla que se debe truncar.
No se admite la visualización de vistas, vistas materializadas, modelos o tablas externas. Las cuotas y los límites de las consultas se aplican a las declaraciones TRUNCATE TABLE
.
Para obtener más información, consulta Cuotas y límites.
Para las tablas particionadas, si la tabla requiere un filtro de partición, falla la declaración TRUNCATE TABLE
. Para truncar la tabla, primero actualiza la tabla a fin de quitar el requisito de filtro de partición y, luego, ejecuta la declaración TRUNCATE TABLE
. Para obtener más información, consulta Actualiza los requisitos del filtro de partición.
Ejemplos de TRUNCATE TABLE
En el siguiente ejemplo, se quitan todas las filas de la tabla llamada Inventory
.
TRUNCATE TABLE dataset.Inventory
Sentencia UPDATE
Usa la declaración UPDATE
cuando quieras actualizar las filas existentes dentro de una tabla.
UPDATE target_name [[AS] alias]
SET set_clause
[FROM from_clause]
WHERE condition
set_clause ::= update_item[, ...]
update_item ::= column_name = expression
Aquí:
target_name
es el nombre de una tabla para la actualización.update_item
es el nombre de la columna que se actualizará y una expresión que se evaluará para el valor actualizado.
Si la columna es tipo STRUCT
, column_name
puede hacer referencia a un campo en STRUCT
mediante la notación de puntos. Por ejemplo, struct1.field1
.
Palabra clave WHERE
Cada declaración UPDATE
debe incluir la palabra clave WHERE
, seguida de una condición.
Para actualizar todas las filas de la tabla, usa WHERE true
.
Palabra clave FROM
Una declaración UPDATE
puede incluir de manera opcional una cláusula FROM
.
Puedes usar la cláusula FROM
para especificar las filas que se actualizarán en la tabla de destino.
También puedes usar columnas de tablas unidas en una cláusula SET
o una condición WHERE
.
La unión de la cláusula FROM
puede ser una unión cruzada si no se especifica ninguna condición en la cláusula WHERE
; de lo contrario, es una unión interna. En cualquier caso, las filas de la tabla de destino pueden unirse con, como máximo, una fila de la cláusula FROM
.
Para especificar el predicado de unión entre la tabla que se actualizará y las tablas en la cláusula FROM
, usa la cláusula WHERE
. Para ver un ejemplo, consulta UPDATE
mediante uniones.
Advertencias:
- La cláusula
SET
puede hacer referencia a columnas de una tabla de destino y columnas de cualquier elementoFROM
en la cláusulaFROM
. Si hay una colisión de nombres, las referencias no calificadas se tratan como ambiguas. - Si la tabla de destino está presente en la cláusula
FROM
como nombre de tabla, debe tener un alias si deseas realizar una unión de tabla con sí misma. - Si una fila de la tabla que se actualizará se une con cero filas de la cláusula
FROM
, entonces la fila no se actualiza. - Si una fila de la tabla que se actualizará se une con una sola fila de la cláusula
FROM
, entonces la fila se actualiza. - Si una fila de la tabla que se actualizará se une con más de una fila de la cláusula
FROM
, la consulta genera el siguiente error de entorno de ejecución:UPDATE/MERGE must match at most one source row for each target row.
Ejemplos de UPDATE
UPDATE
con la cláusula WHERE
En el siguiente ejemplo, se actualiza una tabla llamada Inventory
mediante la reducción del valor del campo quantity
en 10 para todos los productos que contienen la 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 | +-------------------+----------+--------------------+
Después:
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
mediante uniones
En el siguiente ejemplo, se genera una tabla con totales de inventario que incluyen el inventario existente y el inventario de la tabla NewArrivals
, y se marca supply_constrained
como 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)
Si no, puedes unir las tablas:
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 | +-----------------+----------+--------------+
Después:
+-------------------+----------+--------------------+ | 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
campos anidados
En el siguiente ejemplo, se actualizan campos de registro anidados.
UPDATE dataset.DetailedInventory
SET specifications.color = 'white',
specifications.warranty = '1 year'
WHERE product like '%washer%'
Si no, puedes actualizar todo el 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
registros repetidos
En el siguiente ejemplo, se agrega una entrada a un registro repetido en la columna comments
para productos que contienen la 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 | +----------------------+----------+--------------------+----------------------------------------------------+----------------+
Si no, puedes usar la función 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%'
En el siguiente ejemplo, se agrega una segunda entrada al registro repetido en la columna comments
para todas las filas:
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 borrar entradas de valores repetidos, puedes usar 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 | +----------------------+----------+--------------------+----------------------------------------------------+----------------+
Declaración UPDATE
mediante la unión entre tres tablas
En el siguiente ejemplo, se establece supply_constrained
en true
para todos los productos de NewArrivals
en los que la ubicación del depósito está en el 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'
Ten en cuenta que el predicado de unión para la unión con la tabla actualizada (DetailedInventory
) debe especificarse con WHERE
. Sin embargo, las uniones entre las otras tablas (NewArrivals
y Warehouse
) se pueden especificar mediante una cláusula explícita JOIN ... ON
. Por ejemplo, la siguiente consulta es equivalente a la 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 | +--------------+-------+
Después:
+----------------------+----------+--------------------+----------+----------------+ | 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 | +----------------------+----------+--------------------+----------+----------------+
Declaración MERGE
Una declaración MERGE
es una declaración DML que puede combinar las operaciones INSERT
, UPDATE
y DELETE
en una sola instrucción y realizar las operaciones de manera atómica.
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
Donde:
target_name
target_name
es el nombre de la tabla que quieres modificar.
source_name
source_name
es un nombre de tabla o subconsulta.
merge_condition
Una instrucción
MERGE
realiza unaJOIN
entre el destino y la fuente. Luego, según el estado de coincidencia (fila coincidente, solo en la tabla de origen, solo en la tabla de destino), se ejecuta la cláusulaWHEN
correspondiente.JOIN
usamerge_condition
para que coincidan las filas entre las tablas de origen y destino. Según la combinación de las cláusulasWHEN
, se aplican diferentes tiposJOIN
INNER
yOUTER
.Si merge_condition es
FALSE
, el optimizador de consultas evita el uso deJOIN
. Esta optimización se conoce como un predicado falso constante. Un predicado falso constante es útil cuando se realiza unDELETE
atómico en el objetivo más unINSERT
desde una fuente (DELETE
conINSERT
también se conoce como una operaciónREPLACE
).
when_clause
when_clause
tiene tres opciones:MATCHED
,NOT MATCHED BY TARGET
yNOT MATCHED BY SOURCE
. Debe haber al menos unwhen_clause
en cada instrucciónMERGE
.Cada
when_clause
puede tener unsearch_condition
opcional. Elwhen_clause
se ejecuta para una fila si se satisfacen elmerge_condition
y elsearch_condition
. Cuando hay varias cláusulas calificadas, solo se ejecuta la primerawhen_clause
para una fila.
matched_clause
matched_clause
define cómo actualizar o borrar una fila en la tabla de destino si esa fila coincide con una fila en la tabla de origen.Si al menos una
matched_clause
realiza una operaciónUPDATE
, se muestra un error en entorno de ejecución cuando varias filas de la tabla de origen coinciden con una fila de la tabla de destino, y se intenta actualizar o borrar esa fila en la tabla de destino.
not_matched_by_target_clause
not_matched_by_target_clause
define cómo insertar en la tabla de destino si una fila de la tabla de origen no coincide con ninguna fila de la tabla de destino.
not_matched_by_source_clause
not_matched_by_source_clause
define cómo actualizar o borrar una fila de la tabla de destino si no coincide con ninguna fila de la tabla de origen.
Omite nombres de columna
- En
not_matched_by_target_clause
, cuando se omiten los nombres de columna de la tabla de destino, todas las columnas de la tabla de destino se incluyen en orden ascendente en función de sus posiciones ordinales. Ten en cuenta que, si la tabla de destino es una tabla particionada por tiempo de transferencia, se deben especificar los nombres de columna. - En
not_matched_by_target_clause
,ROW
se puede usar para incluir todas las columnas de la fuente en la secuencia ascendente de sus posiciones ordinales. Ten en cuenta que no se incluye ninguna de las seudocolumnas de la fuente. Por ejemplo, la seudocolumna_PARTITIONTIME
no se incluye cuando el origen es una tabla particionada por tiempo de transferencia.
Ejemplos de MERGE
Ejemplo 1
En el siguiente ejemplo, la consulta agrega nuevos elementos de la tabla Inventory
a la tabla DetailedInventory
. Para los artículos con poco inventario, el valor supply_constrained
se establece en true
y se agregan comentarios.
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 son las tablas antes de ejecutar la 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 es la tabla DetailedInventory
después de ejecutar la 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 | +----------------------+----------+--------------------+-------------------------------------------------+----------------+
Ejemplo 2
En el siguiente ejemplo, la consulta combina los elementos de la tabla NewArrivals
en la tabla Inventory
. Si un elemento ya está presente en Inventory
, la consulta ampliará el campo quantity
. De lo contrario, la consulta insertará una fila nueva.
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 son las tablas antes de ejecutar la 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 es la tabla Inventory
después de ejecutar la consulta:
Inventory +-------------------+----------+ | product | quantity | +-------------------+----------+ | dishwasher | 30 | | dryer | 50 | | front load washer | 20 | | microwave | 20 | | oven | 35 | | refrigerator | 25 | | top load washer | 20 | +-------------------+----------+
Ejemplo 3
En el siguiente ejemplo, la consulta aumenta en 20 unidades la cantidad de productos del almacén n.º 1 en la tabla NewArrivals
. La consulta borra todos los demás productos, excepto los del almacén 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 es la tabla NewArrivals
antes de ejecutar la consulta:
NewArrivals +-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 20 | warehouse #2 | | oven | 30 | warehouse #3 | | refrigerator | 25 | warehouse #2 | | top load washer | 10 | warehouse #1 | +-----------------+----------+--------------+
Esta es la tabla NewArrivals
después de ejecutar la consulta:
NewArrivals +-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 20 | warehouse #2 | | refrigerator | 25 | warehouse #2 | | top load washer | 30 | warehouse #1 | +-----------------+----------+--------------+
Ejemplo 4
En el siguiente ejemplo, la consulta reemplaza todos los productos como '%washer%'
en la tabla Inventory
mediante los valores de la tabla 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 son las tablas antes de ejecutar la 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 es la tabla Inventory
después de ejecutar la consulta:
Inventory +-----------------+----------+ | product | quantity | +-----------------+----------+ | dryer | 50 | | microwave | 20 | | oven | 35 | | refrigerator | 25 | | top load washer | 30 | +-----------------+----------+
Ejemplo 5
En el siguiente ejemplo, la consulta agrega un comentario en la tabla DetailedInventory
si el producto tiene una cantidad inferior al promedio en la tabla 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 son las tablas antes de ejecutar la 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 es la tabla DetailedInventory
después de ejecutar la 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 | +----------------------+----------+--------------------+-----------------------------------------------------------------------------------------------+----------------+
Ejemplo 6
En el siguiente ejemplo, la consulta aumenta el inventario de productos del almacén en CA
. Los productos de otros estados se borran y cualquier producto que no esté presente en la tabla NewArrivals
no se modifica.
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 son las tablas antes de ejecutar la 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 es la tabla Inventory
después de ejecutar la consulta:
Inventory +--------------+----------+ | product | quantity | +--------------+----------+ | dryer | 70 | | microwave | 20 | | oven | 35 | | refrigerator | 50 | +--------------+----------+
Ejemplo 7
En el siguiente ejemplo, se muestra un error en entorno de ejecución porque la consulta intenta actualizar una tabla de destino cuando el origen contiene más de una fila coincidente. Para resolver el error, debes modificar merge_condition
o search_condition
a fin de evitar coincidencias duplicadas en la fuente.
MERGE dataset.Inventory T
USING dataset.NewArrivals S
ON T.product = S.product
WHEN MATCHED THEN
UPDATE SET quantity = T.quantity + S.quantity
Estas son las tablas antes de ejecutar la 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 | +--------------+----------+
Cuando ejecuta la consulta, se muestra el siguiente error:
UPDATE/MERGE must match at most one source row for each target row
Ejemplo 8
En el siguiente ejemplo, todos los productos de la tabla NewArrivals
se reemplazan por valores de la subconsulta. La cláusula INSERT
no especifica nombres de columna para la tabla de destino o la subconsulta de origen.
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 es la tabla NewArrivals
antes de ejecutar la consulta:
+-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | dryer | 10 | warehouse #2 | | dryer | 20 | warehouse #1 | | refrigerator | 25 | warehouse #2 | | top load washer | 30 | warehouse #1 | +-----------------+----------+--------------+
Esta es la tabla NewArrivals
después de ejecutar la consulta:
+-----------------+----------+--------------+ | product | quantity | warehouse | +-----------------+----------+--------------+ | microwave | 10 | warehouse #1 | | dryer | 30 | warehouse #1 | | oven | 20 | warehouse #2 | +-----------------+----------+--------------+