Oracle 23c - Joins en DELETE y UPDATE

Otra mejora importante en Oracle Database 23c es la  posibilidad de escribir sentencias DELETE o UPDATE utilicen JOIN entre la tabla a actualizar  o borrar y otras tablas de la base de datos.

Para probar la nueva funcionalidad, vamos a comenzar creando una tabla con 100 filas provenientes de la vista de diccionario de datos "ALL_OBJECTS":

CREATE TABLE MisObjetos AS
SELECT OBJECT_ID, OWNER, OBJECT_NAME, CAST('Desconocido' AS VARCHAR2(128)) AS OBJECT_TYPE
FROM ALL_OBJECTS
ORDER BY 1
FETCH FIRST 100 ROWS ONLY;

Si analizamos el script utilizado, vemos que creamos la columna la columna OBJECT_TYPE llenándola con un valor fijo que no es el existente en la vista original, lo cual podemos comprobar rápidamente con esta sentencia:

SELECT DISTINCT mo.object_type FROM MisObjetos mo;

Como podemos ver, la columna no tiene los valores provenientes de la vista original:

Usando JOIN en UPDATE

Vamos a "corregir" el error cometido al crear la columna OBJECT_TYPE de la tabla, usando una sentencia UPDATE que haga un JOIN contra la vista ALL_OBJECTS:

UPDATE MisObjetos mo
SET mo.object_type = ao.object_type
FROM ALL_OBJECTS ao
WHERE mo.OBJECT_ID = ao.OBJECT_ID;

Podemos ver que la sintaxis implica usar la tabla que debe ser actualizada en la cláusula UPDATE, y se agrega la cláusula FROM con la(s) tabla(s) que necesitamos relacionar con la tabla destino, especificando en la cláusula WHERE la relación entre ellas.

El resultado de ejecutar la sentencia es el siguiente:

100 filas actualizadas.

Y podemos validar que ahora las filas en nuestra tabla tienen la columna actualizada con el valor correcto:

SELECT mo.object_type, COUNT(*)
FROM MisObjetos mo
GROUP BY mo.object_type;

Usando JOIN en DELETE

Ahora vamos a eliminar de nuestra tabla las filas donde el tipo de objeto sea "TABLE", utilizando la siguiente sentencia:

DELETE MisObjetos mo
FROM ALL_OBJECTS ao
WHERE mo.OBJECT_ID = ao.OBJECT_ID
AND ao.object_type = 'TABLE';

la cual devuelve el siguiente mensaje:

8 filas eliminado

Nota: Usamos la columna OBJECT_ID de la vista del diccionario de datos en el WHERE sólo para demostrar la funcionalidad, ya que podríamos haber filtrado por la misma columna en nuestra tabla sin necesidad de establecer el JOIN.

Si validamos de nuevo el contenido de nuestra tabla, podemos ver que ya no tenemos filas con el valor "TABLE"

SELECT mo.object_type, COUNT(*)
FROM MisObjetos mo
GROUP BY mo.object_type;

Sintaxis ANSI

Esta nueva funcionalidad no emplea la sintaxis ANSI para establecer la relación entre la tabla a ser modificada y otra tabla (o vista) de nuestra base de datos, pero sí puede usarse en el resto de la cláusula FROM para relacionar con otras tablas, como se muestra en este ejemplo:

UPDATE MisObjetos mo
SET mo.object_type = ao.object_type
FROM ALL_OBJECTS ao
JOIN ALL_USERS au ON au.USERNAME = ao.OWNER
WHERE mo.OBJECT_ID =ao.OBJECT_ID;

En el ejemplo, usamos el WHERE para relacionar MisObjetos con ALL_OBJECTS, y la sintaxis ANSI para relacionar ALL_USERS con ALL_OBJECTS.

Conclusión

Oracle Database 23c permite escribir sentencias UPDATE y DELETE que utilizan JOIN entre tablas (pero sin usar la sintaxis ANSI) usando la cláusula WHERE para establecer la relación entre la tabla a ser actualizada o borrada y la tabla que deseamos unir.

Si desean conocer más sobre Oracle 23c, es recomendable que vean estos artículos en este blog como punto de partida:

• Oracle Database 23c Free - Developer Edition.
• Instalando Oracle Database 23c (Free Developer Edition) en Oracle Cloud.

Adicionalmente, pueden consultar todos los artículos relacionados a Oracle Database 23c agrupados en en el tag Database 23c.