MERGE — insertar, actualizar o eliminar filas de una tabla condicionalmente
[ WITHconsulta_with[, ...] ] MERGE INTO [ ONLY ]nombre_tabla_destino[ * ] [ [ AS ]alias_destino] USINGfuente_datosONcondición_unióncláusula_when[...] [ RETURNING [ WITH ( { OLD | NEW } ASalias_salida[, ...] ) ] { * |expresión_salida[ [ AS ]nombre_salida] } [, ...] ] dondefuente_datoses: { [ ONLY ]nombre_tabla_origen[ * ] | (consulta_origen) } [ [ AS ]alias_origen] ycláusula_whenes: { WHEN MATCHED [ ANDcondición] THEN {actualización_merge|eliminación_merge| DO NOTHING } | WHEN NOT MATCHED BY SOURCE [ ANDcondición] THEN {actualización_merge|eliminación_merge| DO NOTHING } | WHEN NOT MATCHED [ BY TARGET ] [ ANDcondición] THEN {inserción_merge| DO NOTHING } } yinserción_mergees: INSERT [(nombre_columna[, ...] )] [ OVERRIDING { SYSTEM | USER } VALUE ] { VALUES ( {expresión| DEFAULT } [, ...] ) | DEFAULT VALUES } yactualización_mergees: UPDATE SET {nombre_columna= {expresión| DEFAULT } | (nombre_columna[, ...] ) = [ ROW ] ( {expresión| DEFAULT } [, ...] ) | (nombre_columna[, ...] ) = (sub-SELECT) } [, ...] yeliminación_mergees: DELETE
MERGE realiza acciones que modifican las filas en la
tabla de destino identificada como nombre_tabla_destino,
utilizando la fuente_datos.
MERGE proporciona una única sentencia SQL
que puede condicionalmente hacer INSERT,
UPDATE o DELETE de filas, una tarea
que de otro modo requeriría múltiples sentencias de lenguaje procedimental.
Primero, el comando MERGE realiza una unión (join)
desde la fuente_datos a la tabla de destino,
produciendo cero o más filas de cambio candidatas. Para cada fila de cambio candidata,
el estado de MATCHED,
NOT MATCHED BY SOURCE o
NOT MATCHED [BY TARGET]
se establece una sola vez, después de lo cual las cláusulas WHEN se evalúan
en el orden especificado. Para cada fila de cambio candidata, la primera cláusula que
se evalúe como verdadera se ejecuta. No se ejecuta más de una cláusula WHEN
para ninguna fila de cambio candidata.
Las acciones de MERGE tienen el mismo efecto as
los comandos ordinarios UPDATE, INSERT o
DELETE del mismo nombre. La sintaxis de
esas acciones es diferente, en particular no hay cláusula WHERE
ni se especifica ningún nombre de tabla. Todas las acciones se refieren a la
tabla de destino, aunque se pueden realizar modificaciones en otras tablas utilizando disparadores (triggers).
Cuando se especifica DO NOTHING, se omite la fila de
origen. Dado que las acciones se evalúan en su orden especificado, DO
NOTHING puede ser útil para omitir filas de origen no interesantes antes de
un manejo más detallado.
La cláusula opcional RETURNING hace que MERGE
calcule y devuelva los valores basados en cada fila insertada, actualizada o
eliminada. Se puede calcular cualquier expresión que utilice las columnas de las tablas de origen o
destino, o la función merge_action().
Por omisión, cuando se realiza una acción INSERT o
UPDATE, se utilizan los valores nuevos de las columnas de la tabla de
destino, y cuando se realiza un DELETE, se utilizan los valores
antiguos de las columnas de la tabla de destino, pero también es posible solicitar explícitamente
los valores antiguos y nuevos. La sintaxis de la lista RETURNING es
idéntica a la de la lista de salida de SELECT.
No existe un privilegio MERGE separado.
Si especificas una acción de actualización, debes tener el
privilegio UPDATE en las columnas de la tabla de destino
a las que se hace referencia en la cláusula SET.
Si especificas una acción de inserción, debes tener el privilegio INSERT
en la tabla de destino.
Si especificas una acción de eliminación, debes tener el privilegio DELETE
en la tabla de destino.
Si especificas una acción DO NOTHING, debes tener el privilegio
SELECT en al menos una columna de la tabla de destino.
También necesitarás el privilegio SELECT en cualquier columna de la
fuente_datos y de la tabla de destino a la que se
haga referencia en cualquier condición (incluyendo condición_unión)
o expresión.
Los privilegios se comprueban una vez al inicio de la sentencia y se verifican
sin importar si se ejecutan o no las cláusulas WHEN particulares.
MERGE no se admite si la tabla de destino es una vista
materializada, una tabla foránea o si tiene alguna regla (rule) definida en ella.
consulta_with
La cláusula WITH te permite especificar una o más subconsultas
a las que se puede hacer referencia por nombre en la consulta MERGE.
Consulta la Section 7.8 y la SELECT
para obtener detalles. Ten en cuenta que WITH RECURSIVE no es compatible
con MERGE.
nombre_tabla_destino
El nombre (opcionalmente calificado por esquema) de la tabla o vista de destino en la
cual fusionar (merge). Si se especifica ONLY antes del nombre de la
tabla, las filas coincidentes se actualizan o eliminan solo en la tabla nombrada. Si
no se especifica ONLY, las filas coincidentes también se actualizan o
eliminan en cualquier tabla que herede de la tabla nombrada. Opcionalmente, se puede
especificar * después del nombre de la tabla para indicar explícitamente
que se incluyen las tablas descendientes. La palabra clave ONLY y la
opción * no afectan a las acciones de inserción, las cuales siempre insertan
solo en la tabla nombrada.
Si nombre_tabla_destino es una
vista, debe ser automáticamente actualizable sin disparadores INSTEAD OF,
o debe tener disparadores INSTEAD OF para cada tipo de acción
(INSERT, UPDATE y DELETE)
especificado en las cláusulas WHEN. Las vistas con reglas no están admitidas.
alias_destino
Un nombre sustituto para la tabla de destino. Cuando se proporciona un alias,
este oculta por completo el nombre real de la tabla. Por ejemplo, dado
MERGE INTO foo AS f, el resto de la sentencia MERGE
debe referirse a esta tabla como f y no foo.
nombre_tabla_origen
El nombre (opcionalmente calificado por esquema) de la tabla, vista o tabla de
transición de origen. Si se especifica ONLY antes del nombre de
la tabla, se incluyen las filas coincidentes únicamente de la tabla nombrada. Si no se
especifica ONLY, también se incluyen las filas coincidentes de cualquier
tabla que herede de la tabla nombrada. Opcionalmente, se puede especificar *
después del nombre de la tabla para indicar explícitamente que se incluyen las tablas
descendientes.
consulta_origen
Una consulta (sentencia SELECT o sentencia VALUES)
que proporciona las filas a fusionar en la tabla de destino.
Consulta la sentencia SELECT o la sentencia VALUES
para una descripción de la sintaxis.
alias_origenUn nombre sustituto para la fuente de datos. Cuando se proporciona un alias, este oculta por completo el nombre real de la tabla o el hecho de que se emitió una consulta.
condición_unión
condición_unión es una expresión que
devuelve un valor de tipo boolean (similar a una cláusula
WHERE) que especifica qué filas de la
fuente_datos coinciden con las filas
de la tabla de destino.
Solo las columnas de la tabla de destino que intentan coincidir con las filas de la
fuente_datos deben aparecer en la
condición_unión. Las subexpresiones de la
condición_unión que solo hacen referencia
a las columnas de la tabla de destino pueden afectar la acción que se toma, a menudo
de maneras sorprendentes.
Si se especifican tanto la cláusula WHEN NOT MATCHED BY SOURCE como
la cláusula WHEN NOT MATCHED [BY TARGET], el comando
MERGE realizará una unión completa (FULL join)
entre la fuente_datos y la tabla de destino.
Para que esto funcione, al menos una subexpresión de la condición_unión debe utilizar un operador que admita una
unión hash (hash join), o todas las subexpresiones deben utilizar operadores que
admitan una unión por mezcla (merge join).
cláusula_when
Se requiere al menos una cláusula WHEN.
La cláusula WHEN puede especificar WHEN MATCHED,
WHEN NOT MATCHED BY SOURCE o WHEN NOT MATCHED [BY TARGET].
Ten en cuenta que el estándar SQL solo define
WHEN MATCHED y WHEN NOT MATCHED
(que se define para indicar que no hay ninguna fila de destino coincendente).
WHEN NOT MATCHED BY SOURCE es una extensión del estándar
SQL, al igual que la opción de añadir BY TARGET
a WHEN NOT MATCHED, para hacer su significado más explícito.
Si la cláusula WHEN especifica WHEN MATCHED
y la fila de cambio candidata coincide de una fila de la
fuente_datos a una fila de la
tabla de destino, la cláusula WHEN se ejecuta si la
condición está ausente o si se
evalúa como true.
Si la cláusula WHEN especifica
WHEN NOT MATCHED BY SOURCE y la fila de cambio candidata
representa una fila en la tabla de destino que no coincide con una fila en la
fuente_datos, la cláusula
WHEN se ejecuta si la
condición está ausente o si se
evalúa como true.
Si la cláusula WHEN especifica
WHEN NOT MATCHED [BY TARGET] y la fila de cambio candidata
representa una fila en la fuente_datos
que no coincide con una fila en la tabla de destino, la cláusula WHEN
se ejecuta si la condición está
ausente o si se evalúa como true.
condición
Una expresión que devuelve un valor de tipo boolean.
Si esta expresión para una cláusula WHEN
devuelve true, entonces se ejecuta la acción de esa cláusula
para esa fila.
Una condición en una cláusula WHEN MATCHED puede referirse a columnas
en las relaciones tanto de origen como de destino. Una condición en una cláusula
WHEN NOT MATCHED BY SOURCE solo puede referirse a columnas de la
relación de destino, ya que por definición no hay una fila de origen coincidente.
Una condición en una cláusula WHEN NOT MATCHED [BY TARGET] solo
puede referirse a columnas de la relación de origen, ya que por definición no hay
una fila de destino coincidente. Solo se puede acceder a los atributos del sistema
de la tabla de destino.
inserción_merge
La especificación de una acción INSERT que inserta una fila
en la tabla de destino. Los nombres de las columnas de destino se pueden enumerar
en cualquier orden. Si no se proporciona ninguna lista de nombres de columnas, el
valor por omisión son todas las columnas de la tabla en su orden declarado.
Cada columna que no esté presente en la lista de columnas explícita o implícita se llenará con un valor por omisión, ya sea su valor por omisión declarado o nulo si no hay ninguno.
Si la tabla de destino es una tabla particionada, cada fila se enruta a la partición correspondiente y se inserta en ella. Si la tabla de destino es una partición, se producirá un error si alguna fila de entrada viola la restricción de partición.
Los nombres de las columnas no se pueden especificar más de una vez. Las acciones
INSERT no pueden contener subconsultas.
Solo se puede especificar una cláusula VALUES. La cláusula
VALUES solo puede referirse a columnas de la relación de origen,
ya que por definición no hay ninguna fila de destino coincidente.
actualización_merge
La especificación de una acción UPDATE que actualiza
la fila actual de la tabla de destino.
Los nombres de las columnas no se pueden especificar más de una vez.
No se permiten ni el nombre de la tabla ni una cláusula WHERE.
eliminación_merge
Especifica una acción DELETE que elimina la fila actual
de la tabla de destino. No incluyas el nombre de la tabla ni ninguna otra cláusula,
como lo harías normalmente con un comando DELETE.
nombre_columnaEl nombre de una columna en la tabla de destino. El nombre de la columna se puede calificar con un nombre de subcampo o un subíndice de matriz, si es necesario. (Insertar en solo algunos campos de una columna compuesta deja los otros campos como nulos). No incluyas el nombre de la tabla en la especificación de una columna de destino.
OVERRIDING SYSTEM VALUE
Sin esta cláusula, es un error especificar un valor explícito (que no sea
DEFAULT) para una columna de identidad definida como
GENERATED ALWAYS. Esta cláusula anula esa restricción.
OVERRIDING USER VALUE
Si se especifica esta cláusula, cualquier valor proporcionado para las
columnas de identidad definidas como GENERATED BY DEFAULT
se ignora y se aplican los valores por omisión generados por la secuencia.
DEFAULT VALUES
Todas las columnas se llenarán con sus valores por omisión. (No se permite una
cláusula OVERRIDING en esta forma).
expresión
Una expresión para asignar a la columna. Si se utiliza en una cláusula
WHEN MATCHED, la expresión puede utilizar valores de la
fila original en la tabla de destino, y valores de la fila de la
fuente_datos.
Si se utiliza en una cláusula WHEN NOT MATCHED BY SOURCE,
la expresión solo puede utilizar valores de la fila original en la tabla de destino.
Si se utiliza en una cláusula WHEN NOT MATCHED [BY TARGET],
la expresión solo puede usar valores de la fila de la
fuente_datos.
DEFAULT
Establece la columna a su valor por omisión (que será NULL
si no se le ha asignado ninguna expresión de valor por omisión específica).
sub-SELECT
Una subconsulta SELECT que produce tantas columnas de salida
como se enumeran en la lista de columnas entre paréntesis que la precede. La
subconsulta no debe producir más de una fila cuando se ejecuta. Si produce
una fila, sus valores de columna se asignan a las columnas de destino; si no
produce filas, se asignan valores NULL a las columnas de destino. Si se utiliza
en una cláusula WHEN MATCHED, la subconsulta puede referirse
a valores de la fila original en la tabla de destino, y valores de la fila de
la fuente_datos.
Si se utiliza en una cláusula WHEN NOT MATCHED BY SOURCE,
la subconsulta solo puede referirse a valores de la fila original en la tabla
de destino.
alias_salida
Un nombre sustituto opcional para las filas OLD o
NEW en la lista RETURNING.
Por omisión, los valores antiguos de la tabla de destino se pueden devolver escribiendo
OLD.
u nombre_columnaOLD.*, y los valores nuevos se pueden devolver escribiendo
NEW.
o nombre_columnaNEW.*. Cuando se proporciona un alias, estos nombres se
ocultan y las filas antiguas o nuevas deben referenciarse usando el alias.
Por ejemplo, RETURNING WITH (OLD AS o, NEW AS n) o.*, n.*.
expresión_salida
Una expresión a ser calculada y devuelta por el comando MERGE
después de cambiar cada fila (ya sea insertada, actualizada o eliminada).
La expresión puede utilizar cualquier columna de las tablas de origen o destino, o la
función merge_action()
para devolver información adicional sobre la acción ejecutada.
Escribir * devolverá todas las columnas de la tabla de origen,
seguidas de todas las columnas de la tabla de destino. A menudo, esto conducirá a
una gran cantidad de duplicación, ya que es común que las tablas de origen y
destino tengan muchas de las mismas columnas. Esto se puede evitar calificando
el * con el nombre o alias de la tabla de origen o destino.
Un nombre de columna o * también puede calificarse utilizando
OLD o NEW, o el correspondiente
alias_salida para
OLD o NEW, para hacer que se devuelvan
los valores antiguos o nuevos de la tabla de destino. Un nombre de columna sin
calificar de la tabla de destino, o un nombre de columna o *
calificado utilizando el nombre o alias de la tabla de destino devolverá nuevos
valores para las acciones INSERT y UPDATE, y
valores antiguos para las acciones DELETE.
nombre_salidaUn nombre para usar en una columna devuelta.
Al completarse con éxito, un comando MERGE devuelve una etiqueta de
comando de la forma
MERGE cantidad_total
La cantidad_total es el número total
de filas modificadas (ya sea insertadas, actualizadas o eliminadas).
Si cantidad_total es 0, no se modificó
ninguna fila de ninguna manera.
Si el comando MERGE contiene una cláusula RETURNING,
el resultado será similar al de una sentencia SELECT
que contiene las columnas y valores definidos en la lista RETURNING,
calculados sobre la(s) fila(s) insertada(s), actualizada(s) o eliminada(s) por el comando.
Los siguientes pasos tienen lugar durante la ejecución de MERGE.
Ejecutar cualquier disparador BEFORE STATEMENT para todas
las acciones especificadas, coincidan o no sus cláusulas WHEN.
Realizar una unión (join) de la tabla de origen a la de destino. La consulta resultante se optimizará normalmente y producirá un conjunto de filas de cambio candidatas. Para cada fila de cambio candidata,
Evaluar si cada fila es MATCHED,
NOT MATCHED BY SOURCE o
NOT MATCHED [BY TARGET].
Probar cada condición WHEN en el orden especificado
hasta que una devuelva verdadero.
Cuando una condición devuelve verdadero, realizar las siguientes acciones:
Ejecutar cualquier disparador BEFORE ROW que se
active para el tipo de evento de la acción.
Realizar la acción especificada, invocando cualquier restricción de comprobación (check constraint) en la tabla de destino.
Ejecutar cualquier disparador AFTER ROW que se
active para el tipo de evento de la acción.
Si la relación de destino es una vista con disparadores INSTEAD OF ROW
para el tipo de evento de la acción, se utilizan en su lugar para realizar la
acción.
Ejecutar cualquier disparador AFTER STATEMENT para las
acciones especificadas, ocurran o no realmente. Esto es similar al
comportamiento de una sentencia UPDATE que no modifica ninguna fila.
En resumen, los disparadores a nivel de sentencia para un tipo de evento (por ejemplo,
INSERT) se activarán siempre que especifiquemos
una acción de ese tipo. En cambio, los disparadores a nivel de fila se activarán
únicamente para el tipo de evento específico que se está ejecutando.
Así, un comando MERGE podría activar disparadores a nivel de sentencia
tanto para UPDATE como para INSERT, a pesar de
que solo se activaron los disparadores a nivel de fila de UPDATE.
Debes asegurarte de que la unión produzca a lo sumo una fila de cambio candidata para
cada fila de destino. En otras palabras, una fila de destino no debe unirse a más
de una fila de la fuente de datos. Si lo hace, solo se utilizará una de las filas de
cambio candidatas para modificar la fila de destino; los intentos posteriores de modificar
la fila provocarán un error.
Esto también puede ocurrir si los disparadores de fila realizan cambios en la tabla de
destino y las filas modificadas de esta manera son modificadas posteriormente también por
MERGE.
Si la acción repetida es un INSERT, esto causará una violación
de unicidad, mientras que un UPDATE o DELETE
repetido causará una violación de cardinalidad; este último comportamiento es
requerido por el estándar SQL.
Esto difiere del comportamiento histórico de PostgreSQL
para las uniones en las sentencias UPDATE y
DELETE, donde los intentos segundo y posteriores de modificar la
misma fila simplemente se ignoran.
Si una cláusula WHEN omite una subcláusula AND,
se convierte en la última cláusula alcanzable de ese tipo (MATCHED,
NOT MATCHED BY SOURCE o NOT MATCHED [BY TARGET]).
Si se especifica una cláusula WHEN posterior de ese tipo, sería
demostrablemente inalcanzable y se generaría un error. Si no se especifica una cláusula
final alcanzable de ninguno de los tipos, es posible que no se realice ninguna acción
para una fila de cambio candidata.
El orden en el que se generan las filas desde la fuente de datos es indeterminado
por omisión. Se puede utilizar una consulta_origen
para especificar un orden consistente, si se requiere, lo cual podría ser necesario
para evitar bloqueos mutuos (deadlocks) entre transacciones concurrentes.
Cuando MERGE se ejecuta de forma concurrente con otros comandos que
modifican la tabla de destino, se aplican las reglas habituales de aislamiento de transacciones;
consulta la Section 13.2 para obtener una explicación sobre el
comportamiento en cada nivel de aislamiento.
También puedes considerar el uso de INSERT ... ON CONFLICT como una
sentencia alternativa que ofrece la capacidad de ejecutar un UPDATE
si se produce un INSERT concurrente. Existen diversas diferencias
y restricciones entre los dos tipos de sentencias y no son intercambiables.
Realizar el mantenimiento en customer_accounts basándose en las
nuevas recent_transactions.
MERGE INTO customer_account ca USING recent_transactions t ON t.customer_id = ca.customer_id WHEN MATCHED THEN UPDATE SET balance = balance + transaction_value WHEN NOT MATCHED THEN INSERT (customer_id, balance) VALUES (t.customer_id, t.transaction_value);
Intentar insertar un nuevo artículo de stock junto con la cantidad de stock. Si el artículo ya existe, en su lugar actualizar el recuento de stock del artículo existente. No permitir entradas que tengan stock cero. Devolver detalles de todos los cambios realizados.
MERGE INTO wines w USING wine_stock_changes s ON s.winename = w.winename WHEN NOT MATCHED AND s.stock_delta > 0 THEN INSERT VALUES(s.winename, s.stock_delta) WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN UPDATE SET stock = w.stock + s.stock_delta WHEN MATCHED THEN DELETE RETURNING merge_action(), w.winename, old.stock AS old_stock, new.stock AS new_stock;
La tabla wine_stock_changes podría ser, por ejemplo, una
tabla temporal cargada recientemente en la base de datos.
Actualizar wines basándose en una lista de vinos de reemplazo,
insertando filas para cualquier stock nuevo, actualizando las entradas de stock
modificadas y eliminando los vinos que no estén presentes en la nueva lista.
MERGE INTO wines w USING new_wine_list s ON s.winename = w.winename WHEN NOT MATCHED BY TARGET THEN INSERT VALUES(s.winename, s.stock) WHEN MATCHED AND w.stock != s.stock THEN UPDATE SET stock = s.stock WHEN NOT MATCHED BY SOURCE THEN DELETE;
Este comando cumple con el estándar SQL.
La cláusula WITH, los calificadores BY SOURCE
y BY TARGET para WHEN NOT MATCHED, la
acción DO NOTHING y la cláusula RETURNING
son extensiones del estándar SQL.