Como hacer en SQLServer un update a partir de una select con registros agregados

Una manera muy potente de hacer un Update de una tabla en una base de datos SQL Server es enlazando la tabla con otra con una join, y actualizando los campos de cada registro de la primera a partir de los valores de los campos del registro enlazado de la segunda. Esta técnica ya la comentamos en detalle en otro post como Update con join o update from select en SQL Server.

Update con Join desde tabla con diferente nivel de agregación

Un ejemplo de Update con join sería esta sentencia:

Update tabla1
set tabla1campo1 = t2.tabla2campo1
from tabla1 t1, tabla2 t2
where t1.campoX = t2.campoX

Si el campo por el que se hace la join es clave en la segunda tabla, no hay ningún problema, el update funcionará perfectamente. El problema lo tendremos cuando no sea así y al enlazar por ese campo, a cada registro de la primera tabla le pueda corresponder más de uno de la segunda. En ese caso, podemos encontrarnos con resultados no esperados, ya que la actualización se realizará con el valor de sólo uno de los registros con los que enlace cada registro de la primera tabla.

Si es lo que buscamos ya está bien, pero si la correspondencia es de uno a varios registros y lo que queremos es guardar en cada campo de la primera tabla un valor agregado, como por ejemplo la suma, de todos los registros que tienen correspondencia en la segunda tabla, tendremos que complicar un poco más la sentencia.

 

Cómo hacer un update con join a partir de registros agregados

La primera solución que se nos puede ocurrir es utilizar directamente la función de agregación en el SET, al asignar el valor del campo de la segunda tabla sobre el de la primera. Sería algo así como:

Update tabla1
set tabla1campo1 = sum(t2.tabla2campo1)
from tabla1 t1, tabla2 t2
where t1.campoX = t2.campoX

Hemos añadido una función sum al asignar el segundo campo. Sería genial que SQL Server entendiera así lo que queremos hacer, pero no, el resultado que el motor nos devolvería con esta sentencia de Transact SQL sería el siguiente error:

Mens. 157, Nivel 15, Estado 1, Línea 2      
No puede aparecer un agregado en la lista establecida en una instrucción UPDATE.

 

Y entonces qué hacemos? Pues una solución sencilla es crear una vista o un alias que devuelva al UPDATE el resultado de la agregación de la segunda tabla, y haga que la join del update encuentre en esta vista un sólo registro para cada registro de la tabla a actualizar.

Se trata de crear una vista o un alias de tabla con la join de las dos tablas y el valor agregado para cada registro de la tabla origen sobre la que queremos hacer el update.
La información que necesitamos son los campos clave de cada registro de la tabla, y el valor calculado para cada uno de ellos.

Esta sería la SELECT con los identificadores y el valor agregado para cada registro de la tabla origen:

SELECT campoX, SUM(t2.tabla2campo1)
FROM tabla1 t1, tabla2 t2
WHERE t1.campoX=t2.campoX 
GROUP BY t1.campoX

 

Entonces la sentencia de Update utilizando esta select con un alias quedaría de la siguiente manera:

UPDATE tabla1
SET tabla1campo1 = tabla2agregada.campo1agregado
FROM tabla1 t1, (SELECT campoX, SUM(t2.tabla2campo1) campo1agregado
                          FROM tabla1 t1, tabla2 t2
                          WHERE t1.campoX=t2.campoX
                          GROUP BY t1.campoX)  tabla2agregada
WHERE t1.campoX = tabla2agregada.campoX

 

Y de esta manera en cada registro de la primera tabla, al campo1 se le asignaría la suma de los valores de tabla2campo1 que enlazaran haciendo la join con la otra tabla por campoX.

Puede ser muy útil cuando queremos actualizar campos de tablas con valores de otras tablas que están a diferentes niveles de agregación. Por ejemplo, el importe en una tabla de cabeceras de facturas, calculado a partir de los importes de otra tabla que contiene las lineas o el detalle de esas facturas.

 

¿Conoces o se te ocurre alguna manera más eficiente, más elegante o simplemente distinta de conseguir lo mismo?

 

Por Carlos Fernández

 


Libros de SQL Server

¿Quieres profundizar más en Transact-SQL o en administración de bases de datos SQL? Puedes hacerlo consultando alguno de estos libros de SQL Server.

También puedes revisar la lista completa de los últimos libros de SQL Server publicados en Amazon según lo que te interese aprender


 

Para que quede más claro mejor utilizar el formato de join en lugar de condiciones en el where:

UPDATE tabla1
SET tabla1campo1 = tabla2agregada.campo1agregado
FROM tabla1 t1 
     JOIN (SELECT campoX, SUM(t2.tabla2campo1) campo1agregado
            FROM tabla1 t1, tabla2 t2
            WHERE t1.campoX=t2.campoX
            GROUP BY t1.campoX)  tabla2agregada
     ON t1.campoX = tabla2agregada.campoX 

Al final lo importante es conseguir una asignación de 1 a 1 de los resultados de la consulta agregada a los registros de la tabla que se ha de actulizar.

Otra situación que suele darse es que en lugar de querer actualizar el campo con un valor sumarizado o agregado se necesite informar el valor con el de sólo uno de los diferentes registros que forman parte del agregado.

Entonces no sirve sólo agrupar y aplicar una función de agregado, hay que ordenar cada 'agregado' y seleccionar sólo el primero o el último de los registros del grupo. Eso se puede hacer con la ayuda de las funciones de Ranking, concretamente con row_number() over (partition by .. order by ..).

O sea, que para actualizar con el valor del menor de los registros que enlacen con cada uno de los que queremos actualizar utilizaríamos una consulta como esta:

UPDATE tabla1
SET tabla1campo1 = Tabla2TOP1.campoValor
FROM tabla1 t1 
     JOIN 
        (SELECT campoClave, campoValor
           (SELECT campoClave, campoValor
                  row_number() over (partition by t2.campoClave order by t2.campoOrden) campo1Rankeado
            FROM tabla2 t2)  tabla2rankeada
         WHERE campo1Rankeado=1) Tabla2TOP1
     ON t1.campoClave = Tabla2TOP1.campoClave