18 de septiembre de 2012

Como hacer "upsert" en Oracle de la manera correcta

Una de las operaciones más comunes al trabajar con base de datos es la que incluye las siguientes operaciones:
  1. Actualizar registros existentes de tabla "A" a tabla "B"
  2. Insertar nuevos registros inexistentes de tabla "B" a tabla "A"
Esta tarea es muy común sobre todo en sistemas donde debemos estar migrando información por procesos o batch. Cada motor de base de datos tiene sus formas y por ejemplo en MySql y SQL Server este tipo de operaciones es sencilla o al menos a eso estamos acostumbrados pero, ¿Cómo hacemos esto en Oracle?

La respuesta corta es: No lo haces!



Verán, que vamos a crear un caso partícular que nos sirva de ejemplo. Digamos que tenemos dos tablas, estas tablas son similares y una es una tabla donde recibimos información de cierta entidad y la otra es donde debemos migrar dicha información, entonces tenemos:

Tabla A "MIGRACION_Cliente"


ID Nombre Apellido Fec. de Nac.
101 Emanuel Kant 22/04/1724
102 Torcuato Tasso 11/03/1544
103 Tomas Aquino 01/02/1224
104 Rene Descartes 31/03/1569


Tabla B "SISTEMA_Cliente"


ID Nombre Apellido Fec. de Nac.
101 Emanuel Kant 22/04/1724
102 Torcuato Tasso NULL

Ahora en nuestro caso hipotético lo que deseamos hacer es muy simple: Insertar los dos registros de la Tabla A en B que no existen en esta última y finalmente actualizar la fecha del registro con ID 102. Fácil.

En SQL Server podemos hacer algo tan simple como:


-- Actualizar registros existentes
UPDATE dbo.SISTEMA_Cliente
SET Nombre=c.Nombre,
 Apellido=c.Apellido,
 FechaNacimiento=c.FechaNacimiento
FROM 
(SELECT  a.Nombre ,
     a.Apellido ,
     a.FechaNacimiento 
FROM dbo.MIGRACION_Cliente a
INNER JOIN dbo.SISTEMA_Cliente b
 ON a.ID = b.ID) c;
-- Insertar nuevos registros
INSERT INTO dbo.SISTEMA_Cliente
        ( Nombre, Apellido, FechaNacimiento )
SELECT mc.Nombre, 
 mc.Apellido, 
 mc.FechaNacimiento 
FROM dbo.MIGRACION_Cliente mc
LEFT OUTER JOIN dbo.SISTEMA_Cliente sc
 ON mc.ID = sc.ID
WHERE sc.ID IS NULL;

Para MySQL haríamos algo como lo siguiente:


INSERT INTO sistema_cliente
(ID, Nombre, Apellido, FechaNacimiento)
SELECT ID, Nombre, Apellido, FechaNacimiento
FROM migracion_cliente mc
ON DUPLICATE KEY UPDATE
Nombre = mc.Nombre,
Apellido = mc.Apellido,
FechaNacimiento = mc.FechaNacimiento;

En Oracle:

Para hacer lo mismo en Oracle estaríamos muy tentados a hacer lo siguiente, sobre todo si venimos desde SQL Server u otras bases de datos parecidas a T-SQL (como Sybase):

-- ACTUALIZAR REGISTROS EXISTENTES
UPDATE SISTEMA_CLIENTES
SET NOMBRE = T.NOMBRE,
  APELLIDO = T.APELLIDO,
  FECHANACIMIENTO = T.FECHANACIMIENTO
FROM (
  SELECT MC.NOMBRE,
    MC.APELLIDO,
    MC.FECHANACIMIENTO
  FROM MIGRACION_CLIENTES MC
  INNER JOIN SISTEMA_CLIENTES SC
    ON MC.ID = SC.ID
) T;
-- INSERTAR NUEVOS REGISTROS
INSERT INTO SISTEMA_CLIENTES (
 NOMBRE,
 APELLIDO,
 FECHANACIMIENTO
)
SELECT MC.NOMBRE,
 MC.APELLIDO,
 MC.FECHANACIMIENTO
FROM MIGRACION_CLIENTES MC
LEFT OUTER JOIN SISTEMA_CLIENTES SC
 ON MC.ID = SC.ID
WHERE SC.ID IS NULL;

El problema es que el código para actualizar los registros que es el primer paso no funcionará. Lo que normalmente haremos es buscar en google como hacer dicha actualización y en decenas de sitios encontraremos la siguiente sintaxis:

-- ACTUALIZAR REGISTROS EXISTENTES
UPDATE /*+ bypass_ujvc */
(SELECT MC.NOMBRE AS MC_NOMBRE,
 MC.APELLIDO AS MC_APELLIDO,
 MC.FECHANACIMIENTO AS MC_FECHANACIMIENTO,
 SC.NOMBRE AS SC_NOMBRE,
 SC.APELLIDO AS SC_APELLIDO,
 SC.FECHANACIMIENTO AS SC_FECHANACIMIENTO
FROM MIGRACION_CLIENTES MC
INNER JOIN SISTEMA_CLIENTES SC
 ON MC.ID = SC.ID) T
SET T.SC_NOMBRE = T.MC_NOMBRE,
 T.SC_APELLIDO = T.MC_APELLIDO,
 T.SC_FECHANACIMIENTO = T.MC_FECHANACIMIENTO;

-- INSERTAR NUEVOS REGISTROS
INSERT INTO SISTEMA_CLIENTES (
 NOMBRE,
 APELLIDO,
 FECHANACIMIENTO
)
SELECT MC.NOMBRE,
 MC.APELLIDO,
 MC.FECHANACIMIENTO
FROM MIGRACION_CLIENTES MC
LEFT OUTER JOIN SISTEMA_CLIENTES SC
 ON MC.ID = SC.ID
WHERE SC.ID IS NULL;

Y efectivamente funciona, los registros se actualizan con el primer bloque y se insertan los nuevos con el segundo. El problema es que este código no funcionará en Oracle 10 o mayor. ¿Por qué?

Si ponemos atención en el inicio del update vemos lo siguiente:


UPDATE /*+ bypass_ujvc */


Esto es un "hint" que no es otra cosa que una directiva que nos permite indicarle al compilador interno del motor de la base de datos que reaccione o se comporte de cierta forma, muy similar a los IFDEF de C y compañía. El hint bypass_ujvc es muy común para operaciones de UPDATE con JOIN y como vimos en el ejemplo anterior funciona, pero solo hasta cierto punto. Nos toparemos con pared en el momento que deseemos usar dicho hint en alguna versión actual de la base de datos de Oracle y esto es porque la empresa misma ha desestimado (deprecated) su uso.

De hecho yo mismo me encontré con este problema al estar realizando pruebas, lo noté porque el servidor de producción tenía una versión 10g de la base de datos de Oracle mientras que en mi maquina virtual, por error, instalé la versión 11i pero, a pesar de haberme dado cuenta de ello y de haber podido simplemente instalar la versión anterior decidí investigar porque, efectivamente, ya no funcionaba este hint para hacer un update con join y la respuesta es simple: hay una manera obvia, correcta y sobre todo más simple de hacer este tipo de operaciones en Oracle aunque, como comento, estamos siempre tentados a hacer las cosas pensando en sintáxis de otro producto diferente.

Entonces, después de leer la documentación encontré la función MERGE que nos sirve precisamente para este fin, muy parecido a lo que vimos en el snippet de código de MySQL:

MERGE INTO SISTEMA_CLIENTES SC
USING(SELECT ID,
 NOMBRE,
 APELLIDO,
 FECHANACIMIENTO
 FROM MIGRACION_CLIENTES) MC
ON (SC.ID = MC.ID)
WHEN MATCHED THEN
 UPDATE SET SC.NOMBRE = MC.NOMBRE,
  SC.APELLIDO = MC.APELLIDO,
  SC.FECHANACIMIENTO = MC.FECHANACIMIENTO
WHEN NOT MATCHED THEN
 INSERT (SC.ID, SC.NOMBRE, SC.APELLIDO, SC.FECHANACIMIENTO)
 VALUES (SISTEMA_CLIENTES_SEQ.NEXTVAL, MC.NOMBRE, MC.APELLIDO, MC.FECHANACIMIENTO);

El uso de MERGE INTO es bastante sencillo, y a continuación lo desmenuzo:

MERGE INTO TABLA_DESTINO ALIAS_A
USING (
 -- SELECCIONAMOS COLUMNAS DE LA FUENTE
) ALIAS_B
ON (-- CONDICION DEL JOIN)
WHEN MATCHED THEN
 --SE HACE EL UPDATE AQUI
WHEN NO MTACHED THEN
 -- SE HACE EL INSERT AQUI

Conclusión


Como vemos utilizar MERGE INTO en Oracle es mucho mas fácil y sencillo además de que está optimizado para hacer operaciones con muchos registros. Como tip les recuerdo que siempre que querramos hacer alguna operación en base de datos de la cual desconocemos la sintáxis es mucho mejor revisar la documentación o preguntar a alguien que hacer consultas pensando en otro producto.

1 comentario: