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 hace tiempo en el foro 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.

Mejor revisa la lista completa de los últimos libros de SQL Server publicados en Amazon según lo que te interese aprender, pero estos son los que a mi me parecen más interesantes, teniendo en cuenta precio y temática:

  • eBooks de SQL Server gratuítos para la versión Kindle, o muy baratos (menos de 4€):

  • Libros recomendados de SQL Server


 

 

Hola Buenas tardes,

Hola Buenas tardes, seguramente me falta un dato para poder hacer update.
Quiero actualizar una 2 columnas completas de una tabla y los datos los obtengo de otra que tiene los datos a actualizar.
la tabla productos tiene varias columnas con diferentes datos de esa tabla selecciono productos.models, productos.cantidad, productos.precio, de una tabla nueva llamada actua_produc tengo solo esas 3 columnas necesito que se actualice la columna cantidad y precio con respecto a cada modelo de producto.
Como es la sentencia???
Gracias-

Si en la tabla

Si en la tabla 'actua_product' productos.models es campo clave sólo tendrás que hacer la join por este campo, y actualizar directamente cantidad y precio con los valores de actua_product, sería el caso sencillo.

Si no models no fuera identificador tendrías primero que agrupar por models, en la subquery, y recoger los valores de cantidad y precio para cada modelo utilizando una función de agregación como suma, avg, max, min.. según lo que te interese sacar.

 

Creo que la información de

Creo que la información de este artículo es lo que necesito para poder resolver un problema que tengo para implementar un sp que me actualice un inventario de materia prima, disminuyéndolo a medida que se producen productos.

Los detalles de los productos que se producen quedan grabados en la la tabla ProducionDetalle.

Cada producto que se realiza tiene una composición o formula que se encuentra en una tabla que se llama composición.

Debo disminuir del inventario de MateriaPrima la cantidad que se utilizo para producir los productos que aparecen en ProduccionDetalle.

Como puedo adaptar tu consulta a lo que necesito, es de anotar que el query que yo estaba utilizando es el siguiente y que no funciona por lo que mencionas al principio del artículo:

UPDATE MateriaPrima
SET
MateriaPrima.Inventario = MateriaPrima.Inventario - (ProduccionDetalle.Cantidad * Composicion.Cantidad)
FROM Composicion
INNER JOIN MateriaPrima
ON MateriaPrima.IDMateriaPrima= Composicion.IDMateriaPrima
INNER JOIN Producto
ON Producto.IDProducto= Composicion.IDProducto
INNER JOIN ProduccionDetalle
ON ProduccionDetalle.IDProducto = Producto.IDProducto
INNER JOIN Produccion
ON Produccion.IDProduccion = ProduccionDetalle.IDProduccion
WHERE Produccion.IDProduccion = @IDPRODUCCION

PROGRAMACIÓN DISTRIBUIDA

Enunciado
La empresa “Tortas Negras” necesita un sistema Web para el manejo de la producción de sus diferentes productos, donde se descargue la materia prima de los ingredientes y se aumente el inventario de los productos cuando se realizan diferentes órdenes de producción. Para tal fin se le ha entregado una base de datos que tiene el siguiente diagrama entidad relación:

Descripción de las tablas:
• Unidad de medida: las unidades de medida de los productos y materias primas, por ejemplo: Kilos, Litros, Unidades, ETC
• Materia prima: las materias primas para realizar las tortas, por ejemplo: harina, huevos, leche, ETC.
• Producto: los diferentes productos que vende la empresa.
• Composición: la fórmula para producir dicha torta, por ejemplo: la torta A, se produce con X cantidad del ingrediente Y, X cantidad del ingrediente Z, X cantidad del ingrediente W.
• Producción: es el encabezado de la producción.
• Producción detalle: es el detalla de la producción de productos, por ejemplo: voy a producir 10 tortas A, 20 tortas B, ETC.
Ud. Debe hacer un programa que al momento de hacer una producción, disminuya el inventario de las materias primas según la fórmula de composición de sus ingredientes y aumente el inventario de los productos según las cantidades producidas.

Note que el inventario disminuye de los productos aumenta en las cantidades solicitadas y el inventario de las materias primas disminuye según las cantidades solicitadas y su cantidad en la composición del producto.

No veo la opción para agregar archivos para mostrarte el modelo de la base de datos.
pero practicamente necesito ajustar a mi necesidad lo que explicas en el artículo.

Te agradezo la orientación que me puedas dar.

Estas son las tablas con sus respectivos campos y pk y fk y relaciones

Produccion
------------
IDProduccion (Pk)
Fecha

ProduccionDetalle
------------------
IDProduccionDetalle (Pk)
IDProduccion (Fk)
IDProducto(Fk)
Cantidad

Producto
-----------------
IDProducto (Pk)
Descripcion
IDUnidadMedidad
Inventario
Precio

Composicion
-----------------
IDComposicion (Pk)
IDProducto (Fk)
IDMateriaPrima
Cantidad

MateriaPrima
-----------------
IDMateriaPrima (Pk)
Descripcion
IDUnidadMedeida (Fk)
Inventario

UnidadMedida
-----------------
IDUnidadMedida (Pk)
Descripcion

Relaciones (Produccion - ProduccionDetalle)
-------------------------------------------

Tabla Campo

Produccion IDProduccion (Pk)
ProduccionDetalle IDProduccion (Fk)

Relaciones (ProduccionDetalle - Producto)
-------------------------------------------

Tabla Campo

Producto IDProducto (Pk)
ProduccionDetalle IDProducto (Fk)

Relaciones (Producto - Composicion)
-------------------------------------------

Tabla Campo

Producto IDProducto (Pk)
Composicion IDProducto

Relaciones (MateriaPrima - UnidadMedida)
-------------------------------------------

Tabla Campo

UNidadMedidad IDUnidadMedidad(PK)
MateriaPrima IDUnidad (Fk)

Gente como va? necesito poner

Gente como va? necesito poner en null todos los valores duplicados de un campo de una tabla... y no le encuentro la vuelta??
Para hacer un SELECT aplico este script pero si quiero al mismo ponerlo en un Update no me funciona!! Alguien puede darme una mano?? Mil gracias a todos!!!

select * from tabla where campo1 in

(

select campo1 from tabla where rubro='clie'

group by campo1 having count(*) > 1)

Hola Gustavo Para no liarte

Hola Gustavo

Para no liarte lo mejor es que crees una tabla, o al menos una vista con la listas de campo1 que quieres actualizar, y después con el update con join no deberías tener problema para dejarlos a nulo. Comprueba también que el problema no te venga por alguna restricción con los nulos o algo así.

Asegúrate también de que quieres dejarlo todo a null. Puede que no sea tu caso, pero normalmente, cuando queremos eliminar duplicados lo más complicado es dejar sólo un registro y eliminar todos los que se consideren duplicados. Por si acaso te enlazo otro tema que trata sobre cómo eliminar duplicados en SQLServer

 

--primero insertas los

--primero insertas los valores duplicados a una tabla temporal
select distinct campo1
into #temporal
from tabla where rubro='clie'
group by campo1 having count(*) >1

--haces update en la temporal (los pones nulos)
update #temporal set campo1=null

--De la temporal haces el update a tabla
update tabla
set t2.campo1=t.campo1
from #temporal t, tabla t2
where t2.campo1=t.campo2

drop table #temporal

Espero te sirva amigo, saludos

Es muy buena, esta

Es muy buena, esta publicacion, muchas gracias.

Me pordrias ayudar en los siguiente:

Quiero realizar un Update en los campos de la tabla de Facturas (pago_mes, desc_mes, retenci) con la sumatoria de los campos de la tabla Rec_deta (recibo,descuento, retencion) donde el campo numfact de Factura sea igual a numfact de Rec_deta pero solo para un numero de factura en especifico?

 

El Objetivo es anular un recibo de caja Rec_deta, en el cual hay varias facturas aplicadas y recalcular los abonos a las Facturas afectadas.

 

Muchas gracias de antemano

excelente !!! muchas gracias

excelente !!! muchas gracias por el aporte.

Genial, justo lo que estaba

Genial, justo lo que estaba buscando, gracias!!

Buenos días, gracias por el

Buenos días, gracias por el artículo. Y como sería si se trata de realizar varias actualizaciones en la tabla A tomando el 1er. registro (top 1) de la tabla B. Actualmente lo hago de esta forma :

Update A Set

campo01 = (Select top 1 campo1 from B where A.key1 = B.key1)

campo02 = (Select top 1 campo2 from B where A.key1 = B.key1)

campo03 = (Select top 3 campo1 from B where A.key1 = B.key1)

Si no te importa qué registro

Si no te importa qué registro va a coger el TOP 1 de los que hagan join, que si utilizas un TOP ya entiendo que es así, lo único que tendrías que hacer es utilizar el TOP 1 en la subconsulta en lugar de un group by, o seguir utilizando el group by y como función de agregación utilizar funciones que no sumaricen, como max, min o avg.

Si quisieras quedarte con registros concretos en función de un determinado criterio de ordenación, es decir, actualizar con el valor del primer registro, una vez ordenados por un determinado campo/s, podrías utilizar la función rank() en lugar del group by para quedarte con el valor de un único registro, pero tendrías que hacer un update para cada campo/s que siguiera un criterio de ordenación diferente.

En este otro tema se explican aplicaciones de la función rank(). Echale un vistazo para más detalle, pero la sentencia tipo que podrías utilizar en la vista para quedarte con registros únicos para poder hacer la join sin problema, y controlar el criterio para la elección de ese registro, cosa que con un TOP 1 no puedes garantizar, sería esta:

select * from
( 
  select rank() over (partition by customer order by fecha_alta desc) as Ranking, 
         a.*
  from (select customer, fecha_alta, sum(importe) as importe
        from f_sales where year=2010 group by Customer, fecha_alta ) a
) F_TABLE
where Ranking=1 

En este caso el resultado sería un registro por cada 'customer' con el valor de importe de la fecha de alta más reciente.

 

hola quiero ejecutar una

hola quiero ejecutar una consulta para cambiar el estado de unos pedido respetando lo siguiente para no afectar los demas pedidos

UPDATE PEDIDOS (aca quiero que me busque los pedidos que tienen estado 0)

SET ESTADO = 3 (aca es el estado que le voy a poner)

WHERE CODIGO_PEDIDO LIKE '%CFA%' AND FECHAH_GENERACION LIKE '%2013%'

(con el where pido que sean los pedidos q contiene cfa y fecha 2013) quiero que me expliquen conmo en el update puedo llamar solo los de estado 0 xq segun como tengo ahorita me llama todos los estados, por favor, gracias

Si te he he entendido bien lo

Si te he he entendido bien lo único que tienes que hacer es añadir 'and estado=0' en las condiciones del Where:

UPDATE PEDIDOS
SET ESTADO = 3 
WHERE CODIGO_PEDIDO LIKE '%CFA%' AND FECHAH_GENERACION LIKE '%2013%' 
             AND ESTADO=0

 

Hola Carlos! Me ha servido

Hola Carlos!

Me ha servido mucho esta nota para construir la consulta que necesitaba.

Muchas gracias por tu aporte :)

Saludos!

De nada Hernán, me alegro de

De nada Hernán, me alegro de que estqa información sobre Updates con agregados te haya servido.

Saludos!

Hola, desearia saber como

Hola, desearia saber como quedaria el ejemplo en que usan la funcion sum para sumar varios registros de una tabla hacia orta, pero la misma tabla

No sé si te entiendo bien. El

No sé si te entiendo bien. El sum no se puede hacer sobre la misma tabla porque justo se hace para poder actualizar valores de registros con la suma de valores de más de un registro, por lo que han de ser tablas diferentes..

 

buenas aver si me puedes

buenas aver si me puedes ayudar tengo una tabla con una fecha determinada y desde otra tabla quiero actualizar esa fecha el codigo de aquii me funciono peor el probleme es como puedo aser un en procedimiento alcenado donde pueda poner los parametros como el id de cada tabla

Muchas gracias amigo, me

Muchas gracias amigo, me quitaste un monton de dudas

Me gustaría saber como hago

Me gustaría saber como hago para obtener en otra tabla los nombres completos ejemplo tengo un numero de documento en una tabla y en la otra tengo la lista con las personas. Me gustaría una consulta de actualizacion donde me haga un recorrido por numero de documento y me llene el otro campo los nombres completos... para obtener: Numero de documento, y al lado el campo nombres completos, fecha nacimiento, edad, etc... Gracias.

Tabla_1  Id Nom1 Nom2 Apell1

Tabla_1

 Id

Nom1

Nom2

Apell1

Apell2

Dir

Tel

 

Tabla_2

Docu

Tipo_doc

ID

 

Tabla_3

ID_cliente

Docu

Tipo_doc

Nom1

Nom2

Apell1

Apell2

Dir

Tel

 

Lo que entiendo es lo siguiente, usted con los datos de la tabla_1 y tabla_2, quiere llenar la tabla_3, si es eso, usted tiene una base de datos mal diseñada, pues eso se llama redundancia de datos, otra cosa seria que usted tenga una tabla que almacene temporalmente datos.

Necesito actualizar un

Necesito actualizar un registro de una tabla utilizando parte del mismo registro ejemplo campo número 015 agregar en el mismo campo al registro 015000. pero los registro cambian ejemplo 015,016,017,a todos los registro necesito agregar tres ceros a l final.

Hola. Como hago si tengo dos

Hola.

Como hago si tengo dos tablas, una llamada system_usuarios y otra llamada system_usuarios_log.

La tabla System_usuarios tiene la siguiente estructura:

CREATE TABLE system_usuarios (

    usuario_id integer DEFAULT nextval('system_usuarios_usuario_id_seq'::regclass) NOT NULL,

    usuario character varying(25) DEFAULT ''::character varying NOT NULL,

    nombre character varying(60) DEFAULT ''::character varying NOT NULL,

    descripcion character varying(255) DEFAULT ''::character varying NOT NULL,

    passwd character varying(40) DEFAULT ''::character varying NOT NULL,

    sw_admin character(1) DEFAULT '0'::bpchar NOT NULL,

    activo character(1) DEFAULT '1'::bpchar NOT NULL,

    fecha_caducidad_contrasena timestamp without time zone,

    fecha_caducidad_cuenta timestamp without time zone,

    caducidad_contrasena smallint DEFAULT 0,

    codigo_alterno character varying(30),

    telefono character varying(30),

    tel_celular character varying(30),

    indicativo character varying(3),

    extension character varying(6),

    email character varying(255),

    primer_nombre character varying(20),

    segundo_nombre character varying(30),

    primer_apellido character varying(20),

    segundo_apellido character varying(30),

    firma character varying(30),

    funcion_id integer

);

Mientras que la tabla system_usuarios_log tiene la siguiente estructura:

CREATE TABLE system_usuarios_log (

    log integer DEFAULT nextval('system_usuarios_log_log_seq'::regclass) NOT NULL,

    tipo_log smallint NOT NULL,

    fecha timestamp without time zone DEFAULT now() NOT NULL,

    usuario_id integer NOT NULL,

    detalle character varying(256) DEFAULT ''::character varying NOT NULL,

    ip_address character varying(40) DEFAULT ''::character varying NOT NULL

);

Lo que se requiere es que todos los usuarios, que tenga más de 30 días sin iniciar sesión, se desactiven, es decir que en el campo (activo) pase de ser estado (1=activo) estado (0=desactivado) pero solo los que tengan más de 30 días sin iniciar sesión. Muchas gracias.

 



 

  BI   |    CRM     |    CMS    |    Tendencias en software empresarial    |    Cloud computing  |    Software libre    |   Internet    |    Movilidad y apps