Insertar y actualizar desde una subconsulta

Muchas veces necesitamos actualizar tablas con información procedente de otras tablas; en estos casos puede ser de mucha utilidad el uso de subconsultas anidadas en sentencias INSERT o UPDATE.

Utilizaremos nuevamente el Modelo de base de datos introducido en la primera entrada, y supondremos además que nos solicitan adicionar una nueva tabla que contendrá los datos computados de las ventas totales por sucursales, mensualmente. Presento está nueva tabla, la cual estará enlazada a la tabla sucursales.

Para popular esta nueva tabla utilizaremos una consulta de inserción, donde los valores serán obtenidos de una subconsulta que utiliza funciones de agregación.

INSERT INTO resumen_sucursales (sucursal,anno,mes,ventas)
SELECT o.sucursal, EXTRACT(YEAR FROM o.fecha) AS anno, 
       EXTRACT(MONTH FROM o.fecha) AS mes,
       SUM(a.cantidad*a.precio_unitario)
FROM ordenes o INNER JOIN articulos a ON (o.id_orden=a.orden)
GROUP BY sucursal, anno, mes

Es importante destacar que la subconsulta debe devolver tantas columnas como las contenidas en la cláusula INSERT, y los tipos de datos deben coincidir en el orden en que se presentan.

Supongamos que luego de haber populado la tabla resumen_sucursles, nos solicitan adicionar la columna ordenes, donde se almacenará la cantidad de ordenes realizadas por cada sucursal mensualmente, y queremos actualizar su valor sin tener que eliminar los registros existentes e insertar nuevamente.

En este caso debemos usar una consulta de actualización, utilizando una subconsulta para computar los valores y proveer el dato necesario para la actualización. Una posible solución es la siguiente:

UPDATE resumen_sucursales SET ordenes = (SELECT count(*)
 FROM ordenes
 WHERE EXTRACT(YEAR FROM fecha)=resumen_sucursales.anno 
   AND EXTRACT(MONTH FROM fecha)=resumen_sucursales.mes
   AND sucursal=resumen_sucursales.sucursal)

En este tipo de soluciones se precisa que la subconsulta devuelva solamente un registro, aunque se realizarán internamente tantas subconsultas como combinaciones de valores existan en la tabla resumen_sucursales para las columnas anno, mes y sucursal.

Existen otras variantes para escribir esta consulta de actualización, personalmente prefiero utilizar esta:

UPDATE resumen_sucursales SET ordenes=s.ordenes
FROM 
( SELECT sucursal, EXTRACT(YEAR FROM fecha) AS anno, 
         EXTRACT(MONTH FROM fecha) AS mes, COUNT(*) AS ordenes 
  FROM ordenes
  GROUP BY sucursal, anno, mes
) s 
WHERE s.anno = resumen_sucursales.anno 
  AND s.mes = resumen_sucursales.mes
  AND s.sucursal=resumen_sucursales.sucursal

Algunos gestores permiten utilizar un alias para la tabla que va a ser actualizada, pero otros no, así que tenga cuidado si va a hacer uso del mismo. Por otro lado, la subconsulta si requiere siempre de un alias pues el mismo debe ser utilizado en la cláusula WHERE que enlaza la tabla a actualizar con la subconsulta que contiene los datos que serán utilizados para actualizar. Note que aquí no hay restricciones en cuanto a la cantidad de registros que devuelve la subconsulta, pero si se quieren obtener los resultados correctos debe construirse correctamente la subconsulta y utilizar cuidadosamente la cláusula WHERE. Esta variante de consulta de actualización puede generalizarse para cualquier cantidad de campos, como veremos a continuación, en el que se actualizará además de la cantidad de órdenes, el total de ventas.

UPDATE resumen_sucursales SET ventas=s.ventas, ordenes=s.ordenes
FROM 
( SELECT sucursal, EXTRACT(YEAR FROM fecha) AS anno, 
         EXTRACT(MONTH FROM fecha) AS mes, 
         SUM(a.cantidad*a.precio_unitario) AS ventas, 
         COUNT(DISTINCT o.id_orden) AS ordenes 
  FROM ordenes o INNER JOIN articulos a ON (o.id_orden=a.orden)
  GROUP BY sucursal, anno, mes
) s 
WHERE s.anno = resumen_sucursales.anno 
  AND s.mes = resumen_sucursales.mes 
  AND s.sucursal=resumen_sucursales.sucursal

Note que en este caso debe utilizarse la cláusula DISTINCT al contar la cantidad de órdenes, porque se incluye la tabla articulos que puede contener varios registros por cada orden. En este caso también puede remplazarse la primea línea de la consulta por la siguiente:

UPDATE resumen_sucursales SET (ventas, ordenes)=(s.ventas, s.ordenes)

lo cual es válido también para cualquier cantidad de columnas.

Es todo por hoy, en el próxima entrada estaremos hablando sobre como Limitar los resultados solamente a un lado de la relación cuando se tienen dos o más tablas relacionadas.