Haremos un ejemplo de la funcionalidad MERGE que a grandes rasgos puede actualizar, borrar e insertar en una sola instrucción.
Si antes quieres revisar la documentación de la claúsula MERGE Database SQL Reference, podrás conocer los requisitos y el detalle de la sintaxis, que yo resumiré así:
MERGE INTO TABLA1 USING (TABLA2) ON (CONDICION DE UNION) WHEN MATCHED THEN UPDATE SET columna = expresion WHERE (CONDICION WHEN-MATCHED) DELETE WHERE (CONDICION DELETE) WHEN NOT MATCHED THEN INSERT (columas) VALUES (valores) WHERE (CONDICION NOT-MATCHED)
- TABLA1 puede ser una tabla o una vista y se les conoce como tabla DESTINO
- TABLA2 puede ser una tabla, vista o un subquery, es llamada también ORIGEN, pues de ahí se van a tomar los datos a actualizar o insertar
- CONDICION DE UNION liga ambas tablas
- la sub-sentencia UPDATE, después del SET es muy similar a la tradicional, es decir, se pueden actualizar varias columnas, pero me parece que hay algunas columnas como la usada para la union que parece no pueden actualizarse, eso revisarlo en la documentación.
- le DELETE es una subclausula de WHEN MATCHED THEN UPDATE SET y en cualquier caso, los WHERE se marcan como opcionales.
- el INSERT que no tiene INTO, es similar a la sentencia original, con columnas separadas de los valores y separado por VALUES, y sólo va con la clausula WHEN NOT MATCHED THEN INSERT.
Para nuestro ejemplos usaremos las tablas del módelo HR que viene en la versión Express de Oracle 11g.
Primero crearemos una tabla auxiliar llamada bonuses, pongo un truncate en la lista de las siguientes instrucciones por si desean repetir el ejemplo más de una vez, la llenamos y le quitamos algunos registros para que sea más fácil de ver el resultado:
CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100); truncate table bonuses; INSERT INTO bonuses(employee_id) (SELECT e.employee_id FROM employees e GROUP BY e.employee_id); delete from bonuses where employee_id < 150 or employee_id between 156 and 165 or employee_id > 170; SELECT * FROM bonuses;
Después de ejecutar el script anterior nos quedan los siguientes registros:
EMPLOYEE_ID BONUSY ahora vamos a ejecutar el siguiete MERGE y posteriormente revisaremos los registros que quedan en el tabla (he remarcardo en rojo los que ya no aparecerán):
----------- ----------
150 100
151 100
152 100
153 100
154 100
155 100
166 100
167 100
168 100
169 100
180 100
181 100
182 100
13 filas seleccionadas
MERGE INTO bonuses D USING (SELECT employee_id, salary, department_id FROM employees WHERE department_id = 80) S ON (D.employee_id = S.employee_id) WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01 DELETE WHERE (S.salary > 8000) WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus) VALUES (S.employee_id, S.salary*.5 ) WHERE (S.salary <= 8000);
Que nos arroja:
19 filas fusionadas.Pero si volvemos a ver el listado de la tabla bonues, ya no hay 13 filas, ni 19, depués del MERGE hay 17. Y es que hizo las tres operaciónes antes descritas:
- Se eliminaron los que están remarcados en rojo del listado anterior.
- Se actualizaron los que están remarcados en amarillo del siguiente listado.
- Se insertaron los remarcados en verde del siguiente listado
- Y no se vieron afectados los que están de color azul.
EMPLOYEE_ID BONUS
----------- ----------
153 180
154 175
155 170
159 4000
160 3750
161 3500
164 3600
165 3400
166 164
167 162
171 3700
172 3650
173 3050
179 3100
180 100
181 100
182 100
17 filas seleccionadas
Iremos desglozando la sentencia, y en particular todo gira alededor de los empleados del departamento 80.
MERGE INTO bonuses D USING (SELECT employee_id, salary, department_id FROM employees WHERE department_id = 80) S ON (D.employee_id = S.employee_id) WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01 DELETE WHERE (S.salary > 8000) WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus) VALUES (S.employee_id, S.salary*.5 ) WHERE (S.salary <= 8000);
Iremos desglozando la sentencia, y en particular todo gira alededor de los empleados del departamento 80.
Si son del departamento 80, hay dos clausulas que ejecutar, una, actualizar su bono con el uno porciento de su salario adicional y dos, borrar los que ganen más de 8,000.
MERGE INTO bonuses D USING (SELECT employee_id, salary, department_id FROM employees WHERE department_id = 80) S ON (D.employee_id = S.employee_id) WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01 DELETE WHERE (S.salary > 8000) WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus) VALUES (S.employee_id, S.salary*.5 ) WHERE (S.salary <= 8000);
Si para el caso de cuando los empleados no son del departamento 80, hay una clausula INSERT que ejecutar, misma que pondrá como bono la mitad del sueldo, siempre que sus salarios sean menores de 8,000.
MERGE INTO bonuses D USING (SELECT employee_id, salary, department_id FROM employees WHERE department_id = 80) S ON (D.employee_id = S.employee_id) WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01 DELETE WHERE (S.salary > 8000) WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus) VALUES (S.employee_id, S.salary*.5 ) WHERE (S.salary <= 8000);
Si desas corroborar que los resultados son coherentes, podrías ayudarte de las siguientes sentencias:
- registros eliminados
SELECT * FROM bonuses WHERE employee_id in (SELECT employee_id FROM employees WHERE department_id = 80 and salary> 8000); --registros que fueron eliminados - registros que no cambiaron
SELECT * FROM bonuses WHERE employee_id in (SELECT employee_id FROM employees WHERE department_id != 80 and salary <= 8000); -- registros no modificados - registros que si cambiaron, algunos porque fueron insertados otros sólo cambió el valor.
SELECT * FROM bonuses WHERE employee_id in (SELECT employee_id FROM employees WHERE department_id = 80 and salary <= 8000) order by 1;
1 comentario:
Excelente aporte. Gracias.
Publicar un comentario