Declaraciones de lenguaje de manipulación de datos (DML) en SQL estándar

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 Usa el 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"}} |
+-------------------+----------+--------------------+-------------------------------------------------+----------------------------------------------------------------------------------------------------+

INSERT con tipos ARRAY

En el siguiente ejemplo, se muestra cómo insertar una fila en una tabla, en la que uno de los campos es un tipo ARRAY.

CREATE TABLE IF NOT EXISTS dataset.table1 (names ARRAY<STRING>);

INSERT INTO dataset.table1 (names)
VALUES (["name1","name2"])

Esta es la tabla después de ejecutar la consulta:

+-------------------+
|       names       |
+-------------------+
| ["name1","name2"] |
+-------------------+

Declaración DELETE

Usa la declaración DELETE cuando quieras quitar filas de una tabla.

DELETE [FROM] target_name [alias]
WHERE condition

Para borrar todas las filas de una tabla, usa la declaración TRUNCATE TABLE.

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.

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.

Nota: Esta sentencia es una operación de metadatos y no tiene un cargo.

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.

Precaución: La ejecución de esta declaración quita cualquier política de acceso a nivel de fila en la tabla.

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 elemento FROM en la cláusula FROM. 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

Nota: El predicado de unión entre Inventory y NewArrivals se especifica mediante la cláusula WHERE.

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.

Nota: Si como parte de MERGE, se inserta una fila nueva en la tabla de destino, la fila recién insertada no será apta para una coincidencia con las filas de la tabla de origen. La coincidencia se basa en el estado en que se encuentran las tablas cuando se inicia la consulta.

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

Aquí:

• 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 una JOIN 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áusula WHEN correspondiente. JOIN usa merge_condition para que coincidan las filas entre las tablas de origen y destino. Según la combinación de las cláusulas WHEN, se aplican diferentes tipos JOIN INNER y OUTER.

  Si merge_condition es FALSE, el optimizador de consultas evita el uso de JOIN. Esta optimización se conoce como un predicado falso constante. Un predicado falso constante es útil cuando se realiza un DELETE atómico en el objetivo más un INSERT desde una fuente (DELETE con INSERT también se conoce como una operación REPLACE).

• when_clause

  when_clause tiene tres opciones: MATCHED, NOT MATCHED BY TARGET y NOT MATCHED BY SOURCE. Debe haber al menos un when_clause en cada instrucción MERGE.

  Cada when_clause puede tener un search_condition opcional. El when_clause se ejecuta para una fila si se satisfacen el merge_condition y el search_condition. Cuando hay varias cláusulas calificadas, solo se ejecuta la primera when_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ón UPDATE, 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 |
+-----------------+----------+--------------+