La sentencia MERGE, a la que muchos denominan UPSERT debido a su funcionalidad, está disponible desde la aparición de la versión 9i de la base de datos Oracle. Se trata de una de las funcionalidades del kernel de Oracle más utiles a la hora de permitir el uso de la tecnología ETL (Extract, Transform and Load - Extraer, Transformar y Cargar) con las bases de datos Oracle. Este tipo de tecnología puede utilizarse y está especialmente enfocada para ser usada en aplicaciones de data warehousing (almacen de datos). Básicamente, lo que permite la sentencia SQL MERGE es, dependiendo de una condición lógica, actualizar registros (UPDATE) cuando la condición se cumple, o insertar registros (INSERT) cuando dicha condición no se cumple, de ahí surge la denominación de UPSERT.
Anteriormente a la versión 9i de Oracle, la alternativa en SQL era ejecutar dos sentencias DML, un UPDATE y un INSERT, cada una utilizando condiciones lógicas opuestas. En cuanto a las alternativas en PL/SQL eran: bien intentar insertar un registro y si la sentencia INSERT fallaba debido a una excepción PLSQL del tipo DUP_VAL_ON_INDEX, entonces realizar un UPDATE del registro en cuestión; bien intentar actualizar un registro y si la sentencia UPDATE devolvía SQL%NOTFOUND, entonces ejecutar la correspondiente sentencia INSERT.
Las ventajas de la sentencia SQL MERGE son claras:
- Permite con una misma sentencia realizar un UPDATE si el registro existe, o un INSERT si se trata de un nuevo registro.
- La sentencia puede paralelizarse de forma transparente.
- Se evita la necesidad de realizar actualizaciones múltiples.
- Es especialmente útil para realizar operaciones en masa y, como ya he mencionado, en aplicaciones de data warehousing.
- El rendimiento de la base de datos mejora ya que, al necesitarse menos sentencias SQL para realizar las mismas operaciones, también se necesitan menos accesos a las tablas fuente.
Ejemplo de sentencia MERGE
En el presente ejemplo voy a partir del hecho de que tenemos dos tablas en un data warehouse, una de ellas con muchos registros denominada clientes y otra más pequeña denominada datos_cli cuyos registros deben insertarse en la tabla clientes. En pocas palabras, estoy suponiendo que la tabla datos_cli contiene los cambios a realizar sobre la tabla clientes. La sentencia SQL MERGE que debemos utilizar para actualizar la tabla cliente podría ser la siguiente:
MERGE INTO clientes cli USING datos_cli dac ON (cli.cliente_id = dac.cliente_id) WHEN MATCHED THEN UPDATE SET cli.nombre = dac.nombre, cli.direccion = dac.direccion WHEN NOT MATCHED THEN INSERT (cliente_id, nombre, direccion) VALUES (dac.cliente_id, dac.nombre, dac.direccion);
Así pues, la sentencia MERGE del ejemplo realizará las siguientes operaciones:
- Si existe un registro en datos_cli con el mismo ID de otro registro en la tabla clientes, entoces actualizará el valor de los campos nombre y dirección de la tabla clientes con los valores correspondientes de la tabla datos_cli.
- Si no existe un registro en clientes con el ID del registro de datos_cli que está siendo procesado, entonces se insertará dicho registro en la tabla clientes.
En resumen, la sintaxis de la sentencia MERGE debe incorporar:
Una cláusula INTO, que especifica la tabla destino donde los registros serán actualizados o insertados.
Una cláusula USING, especificando el origen de los datos que van a ser insertados o que van a servir para actualizar la tabla destino. El origen de los datos puede tratarse de una tabla, una vista, o del resultado de la ejecución de una consulta SELECT.
Una cláusula ON, que especifica la condición bajo la cual se realizará, bien la operación UPDATE (si la condición se cumple), bien la operación INSERT (si la condición no se cumple).
Las cláusulas WHEN MATCHED | NOT MATCHED, que son las que indican a la base de datos Oracle que acción realizar si se cumple o no se cumple la condición del ON. Estas dos cláusulas se pueden poner en cualquier orden.
Artículos relacionados:
La sentencia INSERT multitabla de la versión 9i de Oracle.
La cláusula WITH de la versión 9i de la base de datos Oracle.
13 comentarios:
Vaya, la semana pasada vi esta instrucción en un paquete de miles de líneas de código y como tenía tanta prisa por me quedé con la intriga de saber qué hacía. Ahora me lo has resuelto. Muchas gracias ;)
Excelente explicación, muchas gracias.
Muy útil. Gracias.
Muchas gracias por el aporte. Excelente explicación. Saludos!
Muy buena explicación, muchas gracias.
HOla quisiera usar esta sentencia para una tabla, pero me da un error
Error: ORA-00001: unique constraint (SYSTEM.REG_ID_PK) violated
yo solo quiero pasarle por parametro un ID y si esta en la tabla que me actualize ese ID y sino que me lo cree nuevo.
DECLARE
v_region NUMBER;
BEGIN
v_region := 7;
MERGE INTO regions reg
USING (SELECT * FROM regions) r
ON (r.region_id = v_region)
WHEN MATCHED THEN UPDATE SET region_name = 'XXX'
WHEN NOT MATCHED THEN INSERT(region_id,region_name)
VALUES(v_region,'Antartida');
dbms_output.put_line('Registros actualizados: '||sql%rowcount);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error: '||SQLERRM);
END;
DECLARE
v_region NUMBER;
BEGIN
v_region := 7;
MERGE INTO regions reg
USING (SELECT * FROM regions) r
ON (r.region_id = v_region)
WHEN MATCHED THEN UPDATE SET region_name = 'XXX'
WHEN NOT MATCHED THEN INSERT(region_id,region_name)
VALUES(v_region,'Antartida');
dbms_output.put_line('Registros actualizados: '||sql%rowcount);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error: '||SQLERRM);
END;
--DATOS DE LA TABLA REGIONS
1
Europe
399
-------
2
Americas
499
------
3
Asia
499
-----
4
Middle East and Africa
499
-----
5
Oceania
699
-----
Hola Gregory,
La sintaxis que utilizas es incorrecta. Prueba esto:
MERGE INTO regions reg
USING (SELECT 7 region_id FROM DUAL) r
ON (r.region_id = reg.region_id)
WHEN MATCHED THEN UPDATE SET region_name = 'XXX'
WHEN NOT MATCHED THEN INSERT(region_id,region_name)
VALUES(r.region_id,'Antartida');
Un saludo.
Se podría hacer un merge solo con la clásula WHEN MATCHED THEN y sin la cláusula WHEN NOT MATCHED ? es decir, solo que actualice, si si no matchea que nada haga?
Sí, es posible utilizar sólo una de las cláusulas, tanto sólo WHEN MATCHED THEN como sólo WHEN NOT MATCHED THEN.
Pero entonces la cláusula MERGE no sería realmente un MERGE. Si se utiliza sólo la cláusula WHEN MATCHED THEN, sería realmente un UPDATE y sería más adecuado utilizar la sentencia UPDATE, y si sólo se utiliza WHEN NOT MATCHED THEN, sería realmente un INSERT y sería más adecuado utilizar la sentencia INSERT.
Muchas gracias JPLM, tienes toda la razón.
Muy bien explicado, claro y conciso. Gracias
exacto, insert o update con where y listo!!
Publicar un comentario