Así como podemos consultar filas de forma recursiva, también podemos actualizar esas filas recursivamente si es lo que necesitamos.
Veamos un caso:
Tenemos una tabla llamada CUENTAS que tiene estos datos:
Captura 1. Si haces clic en la imagen la verás más grande
En la columna CUE_IDENTI guardamos el identificador de cada fila; en la columna CUE_IDEPAD guardamos el identificador de su fila padre; en la columna CUE_TIMEST guardamos la fecha y la hora de la última actualización de una cuenta. Ahora supongamos que queremos que la cuenta CRÉDITOS y todas las cuentas que sean sus descendientes tengan la fecha y la hora actuales.
Listado 1.
UPDATE
CUENTAS
SET
CUE_TIMEST = CURRENT_TIMESTAMP
WHERE
CUE_IDENTI IN (
WITH RECURSIVE MiCTE AS (
SELECT
CUE_IDENTI
FROM
CUENTAS
WHERE
CUE_IDENTI = 6
UNION ALL
SELECT
T1.CUE_IDENTI
FROM
CUENTAS T1
JOIN
MiCTE
ON T1.CUE_IDEPAD = MiCTE.CUE_IDENTI
)
SELECT CUE_IDENTI FROM MiCTE
);
Captura 2. Si haces clic en la imagen la verás más grande
Tal y como podemos ver en la Captura 2. se actualizó la columna CUE_TIMEST de la cuenta que tiene CUE_IDENTI =6 y también de todos sus descendientes. En el Listado 1. podemos ver que la primera cuenta a ser actualizada es la que tiene CUE_IDENTI = 6, a continuación se actualizan las demás filas.
Desde luego que no solamente podemos cambiar la fecha y la hora al actualizar, podemos cambiar cualquier dato que necesitemos.
Explicación:
Como en la cláusula WHERE del UPDATE estamos usando IN eso significa que queremos actualizar varias filas. ¿Qué filas actualizaremos? Aquellas cuya columna CUE_IDENTI se encuentre en la tabla virtual que crearemos. En nuestra tabla virtual la primera fila es la que tiene CUE_IDENTI = 6, así que esa fila sí o sí será actualizada. A continuación hallamos el CUE_IDENTI de las demás filas que queremos actualizar. Como la tabla virtual es recursiva entonces se le irán agregando filas hasta que la condición del JOIN deje de cumplirse. El último SELECT es el que devuelve todas las filas de la tabla virtual, y todas esas filas tienen una sola columna, llamada CUE_IDENTI.
Entonces, nuestro UPDATE recursivo sería el equivalente a este UPDATE no recursivo.
Listado 2.
UPDATE CUENTAS SET CUE_TIMEST = CURRENT_TIMESTAMP WHERE CUE_IDENTI IN (6, 7, 8, 9, 10)
¿Cuál es la diferencia? Que en el Listado 2. debemos conocer todos los identificadores que deseamos actualizar, en cambio en el Listado 1., no. Allí solamente necesitamos conocer el identificador de la primera cuenta, nada más. Todas las cuentas que sean sus descendientes serán actualizadas, sean 4 como en este caso o sean 500 o sean 1000. La única limitación es que pueden ser como máximo 1024 porque el Firebird permite hasta 1024 recursiones.
El Listado 2. es más sencillo, pero requiere conocer los identificadores de todas las cuentas que queremos actualizar y si los usuarios están constantemente agregando cuentas tendremos que estar cambiando nuestro UPDATE a cada rato. Por el contrario con el Listado 1. lo único que debemos conocer es el identificador de la primera cuenta que deseamos actualizar y todas las cuentas que sean sus descendientes se actualizarán también.
Artículos relacionados:
Entendiendo a las tablas autoreferenciadas
Usando CTE (Common Table Expression)
Otro ejemplo de CTE: ventas semanales
Usando varias CTE en una vista o en un stored procedure
Ejemplo de recursión (1). Filas salteadas
Ejemplo de recursión (2). Numerar filas
Ejemplo de recursión (3). Fechas consecutivas
Firebird SQL 
Sep 29, 2017 @ 21:26:19
Antes que nada. Mi agradecimiento por tu gran aporte al conocimiento de Firebird.
Te comento que estoy usando FB 2.5 y me encontré con un problema
En un CTE recursivo, con le sig. estrutura, no me realiza eL LEFT JOIN final. En su lugar me realiza JOIN.
with horas recursive as (
select 8 as hora
from rdb$database
union all
select hora + 1
from horario
where horario.hora < 20
)
,
ocupacion as (
Select …
)
select horas.hora, f1,…
from horas
LEFT JOIN ocupacion on horas.hora between ocupacion.horaini and ocupación.horafin
Con lo cual solo obtengo las horas ocupadas, en lugar de las disponibles.
Agredeceria cualquier comentario.
Gracias
Oct 01, 2017 @ 00:58:33
Hola
El problema es que estás usando BETWEEN en el LEFT JOIN. En el LEFT JOIN deberías poner una igualdad y la condición en la cláusula WHERE. En tu caso quizás el LEFT JOIN está de más y deberías reemplazarlo por un WHERE.
Saludos.
Walter.
Oct 02, 2017 @ 11:12:23
Que tal Walter.
En mi humilde opinión no debería existir diferencia en donde se coloca la condición. Mas alla de esto, haciendo pruebas, puedo afirmar que cuando en el select final se coloca una condición where para la tabla ‘join’ no se mantiene el left join, aun cuando todos los registro cumplan la condición.
with recursive
aux as (
select 0 as n
from rdb$database
union all
select aux.n + 1
from aux
where aux.n <= 30
)
select aux.n, mitabla.id
from aux
left join mitabla on aux.n between mitabla.id and mitabla.id +1
where mitabla.id is not null
Donde mitabla.id es primary key
Si elimino la condision where funciona correctamente
Si pongo "where aux.n<20" funciona correctamente