MySQL - Hacer UPDATE de un SubSelect agrupado

Pos: 40

Val: 22

Hacer UPDATE de un SubSelect agrupado

Publicado por kachu (12 intervenciones) el 16/11/2017 12:47:14

Hola, Necesito hacer un update de un SubSelect agrupado. Tengo esta consulta que me devuelve dos columnas; producto y las Existencias (diferencia entre compra y venta) .

SELECT g.Producto,SUM(g.Compras-g.ventas) Existencias FROM
(select Producto,0 as Compras,SUM(Cantidad) Ventas FROM lineasventas GROUP BY Producto
UNION
select Producto,SUM(Cantidad) Compras,0 Ventas FROM lineascompras GROUP BY Producto
) AS g
GROUP BY Producto

Esto me devuelve correctamente:
Producto Existencias
P-1 = 15
P-2 = 7
P-3 = 30
.... = ...
.... = ...
P-1000 = xx

Necesito actualizar una tabla stock con las Existencias retornadas de esta consulta. Intento lo siguiente pero no logro hacer el JOIN con la subconsulta para vincularlo con la tabla stocks

UPDATE stocks SET Actual=
(SELECT SUM(g.Compras-g.ventas) Existencias FROM
(select Producto,0 as Compras,SUM(Cantidad) Ventas FROM lineasventas GROUP BY Producto
UNION
select Producto,SUM(Cantidad) Compras,0 Ventas FROM lineascompras GROUP BY Producto
) AS g
GROUP BY Producto)

Agradezco la ayuda.
Saludos..

Valora esta pregunta

Me gusta: Está pregunta es útil y esta clara

No me gusta: Está pregunta no esta clara o no es útil

Responder

Pos: 1

Val: 953

Hacer UPDATE de un SubSelect agrupado

Publicado por leonardo_josue (414 intervenciones) el 16/11/2017 16:01:34

Hola kachu:

En tu UPDATE en realidad no estás poniendo cómo se relacionan tus tabla stock con tu subconsulta, así es que aunque no lo dices explícitamente en tu post, seguramente estás obteniendo algún tipo de error que diga que la subconsulta está regresando más de un registro o algo parecido.

Ojo con eso, siempre que publiques un post, cuando nos digas que algo de lo que intentaste hacer no funciona, dinos también por qué no funciona, si la consulta marca algún error, entonces postea el error y si la consulta no hace lo que esperas, entonces explica el resultado de la ejecución y dinos qué está mal.

Ahora, volviendo a tu problema, como te comento, en realidad no estás haciendo ningún JOIN entre tus tablas. voy a poner un ejemplo de qué tendrías que hacer para ver si queda claro. Supongamos que tienes una tabla así:

mysql> SELECT id_producto, cantidad FROM stock;

+-------------+----------+

| id_producto | cantidad |

+-------------+----------+

|           1 |       10 |

|           2 |       20 |

|           3 |       30 |

|           4 |       40 |

|           5 |       50 |

+-------------+----------+

5 rows in set (0.02 sec)

Ahora, voy a SIMULAR tu subconsulta, para regresar algunas cantidades que se tengan que actualizar, así:

mysql> SELECT 1 id_producto, 11 cantidad

    -> UNION

    -> SELECT 3 id_producto, 35 cantidad

    -> UNION

    -> SELECT 5 id_producto, 60 cantidad;

+-------------+----------+

| id_producto | cantidad |

+-------------+----------+

|           1 |       11 |

|           3 |       35 |

|           5 |       60 |

+-------------+----------+

3 rows in set (0.00 sec)

esto quiere decir que son tres productos los que tienen un nuevo stock. Ahora, hacemos un INNER JOIN para juntar las tablas de STOCK y la SUBCONSULTA:

mysql> SELECT

    ->   S.id_producto,

    ->   S.cantidad stock_inicial,

    ->   T.cantidad stock_final

    -> FROM

    ->   stock S

    -> INNER JOIN

    -> (  SELECT 1 id_producto, 11 cantidad

    ->    UNION

    ->    SELECT 3 id_producto, 35 cantidad

    ->    UNION

    ->    SELECT 5 id_producto, 60 cantidad

    -> ) T ON S.id_producto = T.id_producto;

+-------------+---------------+-------------+

| id_producto | stock_inicial | stock_final |

+-------------+---------------+-------------+

|           1 |            10 |          11 |

|           3 |            30 |          35 |

|           5 |            50 |          60 |

+-------------+---------------+-------------+

3 rows in set (0.00 sec)

Entonces aquí vemos que las tablas están correctamente relacionadas. Lo único que falta es "TRANSFORMAR" esta consulta para convertirla en un UPDATE:

mysql> UPDATE

    ->   stock S

    -> INNER JOIN

    -> (  SELECT 1 id_producto, 11 cantidad

    ->    UNION

    ->    SELECT 3 id_producto, 35 cantidad

    ->    UNION

    ->    SELECT 5 id_producto, 60 cantidad

    -> ) T ON S.id_producto = T.id_producto

    -> SET S.cantidad = T.cantidad;

Query OK, 3 rows affected (0.08 sec)

Rows matched: 3  Changed: 3  Warnings: 0

y finalmente comparamos que los cambios se hicieron correctamente:

mysql> SELECT id_producto, cantidad FROM stock;

+-------------+----------+

| id_producto | cantidad |

+-------------+----------+

|           1 |       11 |

|           2 |       20 |

|           3 |       35 |

|           4 |       40 |

|           5 |       60 |

+-------------+----------+

5 rows in set (0.00 sec)

Haz la prueba y nos comentas.

Saludos
Leo.

Valora esta respuesta

Me gusta: Está respuesta es útil y esta clara

No me gusta: Está respuesta no esta clara o no es útil

Comentar

Pos: 40

Val: 22

Hacer UPDATE de un SubSelect agrupado

Publicado por Jesus (12 intervenciones) el 16/11/2017 21:04:58

Leo, lo primero agradecer tu pronta y detallada respuesta, y a continuación disculparme por no haber expuesto correctamente mi post, posiblemente debido a mi inexperiencia en los foros ya que es la primera vez que participo en un foro.
He intentado llevar tu ejemplo a mi caso concreto y no obtengo los resultados deseados. Parto de que la consulta para obtener el
Stock resultante de las existencias es correcta (ejemplo en la imagen adjunta) ya que me da los resultados esperados.
El caso real parte de 3 tablas; stocks, lineascompras y lineasventas. La complejidad es que los productos de las tablas de lineas que son los detalles de los albaranes están repetidos de distintas compras y ventas con lo cual tengo que sumar agrupándolos.

update_subselect0

SELECT g.Producto,SUM(g.Compras),SUM(g.Ventas),SUM(g.Compras-g.ventas) Stock FROM stocks s INNER JOIN
(select Producto,0 as Compras,SUM(Cantidad) Ventas FROM lineasventas GROUP BY Producto
UNION
SELECT Producto,SUM(Cantidad) Compras,0 Ventas FROM lineascompras GROUP BY Producto
) AS g ON g.Producto=s.Producto
GROUP BY Producto
update_subselect1

Lo que no se hacer es el GROUP BY en el UPDATE lo intente con:

UPDATE stocks s INNER JOIN
(select Almacen,Producto,0 as Compras,SUM(Cantidad) Ventas FROM lineasventas GROUP BY Producto
UNION
select Almacen,Producto,SUM(Cantidad) Compras,0 Ventas FROM lineascompras GROUP BY Producto
) g ON g.Producto=s.Producto
SET s.Stock=g.Compras-g.Ventas

Este update no da ningun error pero no graba los datos correctos solo actualiza una suma g.Ventas lo correcto seria SUM(g.Compras-g.Ventas) pero al hacer esto me sale error "Invalid use of group function" logicamente este error es porque no hay un GROUP BY Producto como tengo en el SELECT. Entiendo que el problema tiene que estar en GROUP BY Producto pero si lo pongo me da error de Sintaxis en GROUP BY

UPDATE stocks s INNER JOIN
(select Almacen,Producto,0 as Compras,SUM(Cantidad) Ventas FROM lineasventas GROUP BY Producto
UNION
select Almacen,Producto,SUM(Cantidad) Compras,0 Ventas FROM lineascompras GROUP BY Producto
) g ON g.Producto=s.Producto
SET s.Stock=SUM(g.Compras-g.Ventas)
GROUP BY Producto

Espero haberme explicado un poco mejor, si no es así, pido disculpas por anticipado.
Gracias.

Valora esta respuesta

Comentar

Pos: 1

Val: 953

Hacer UPDATE de un SubSelect agrupado

Publicado por leonardo_josue (414 intervenciones) el 16/11/2017 23:07:36

Hola de nuevo Jesús:

En realidad estás muy cerca de la respuesta, pero sí tienes un pequeño problema. Cuando yo puse el UNION, te dije que era para SIMULAR tu consulta, esto quiere decir, que en la subconsulta deberías de poner la consulta que te arroja el resultado FINAL de que esperas actualizar... y sobre esa tabla entonces HACER EL JOIN hacia la tabla stocks... en otras palabras, el UPDATE debe quedar más o menos así:

mysql> UPDATE stocks S INNER JOIN

    -> (  SELECT

    ->      g.Producto, SUM(g.Compras-g.ventas) Stock

    ->    FROM

    ->    ( SELECT Producto,0 AS Compras,SUM(Cantidad) Ventas

    ->      FROM lineasventas

    ->      GROUP BY Producto

    ->      UNION

    ->      SELECT Producto,SUM(Cantidad) Compras,0 Ventas

    ->      FROM lineascompras

    ->      GROUP BY Producto

    ->    ) AS g

    ->    GROUP BY Producto

    -> ) T ON T.producto = S.producto

    -> SET S.Stock = T.stock;

Query OK, 0 rows affected (0.04 sec)

Rows matched: 4  Changed: 0  Warnings: 0

mysql> SELECT * FROM Stocks;

+----------+-------+

| producto | stock |

+----------+-------+

| P-1      |     1 |

| P-2      |     3 |

| P-3      |    -1 |

| P-4      |     5 |

| P-5      |     7 |

+----------+-------+

5 rows in set (0.00 sec)

Observa que toda las subconsulta con el alias T, te entrega los valores FINALES que deben tener los productos en STOCK, entonces, el inner join lo haces hasta este punto.

haz la prueba y nos comentas.

Saludos
Leo.

Valora esta respuesta

Comentar

Pos: 40

Val: 22

Hacer UPDATE de un SubSelect agrupado

Publicado por Jesus (12 intervenciones) el 17/11/2017 08:51:42

Perfecto!!
Leo;
No solo la ejecución de tu consulta es correcta sino que tu explicación es brillante. Me dices que estaba “cerca” para mi tan lejos que por primera vez me ha llevado a la participación en un foro para exponer mi problema.
Llevo algunos años programando, al principio pensé que sabía mucho, conforme pasaba el tiempo me di cuenta que sabia mas bien poco, con los años tengo muy claro que es mas lo que ignoro que lo que conozco (en lo que a programación se refiere).
Muchas gracias por enseñarnos, si alguna vez nos conocemos o vienes por Vigo estas invitado.
Saludos..

Valora esta respuesta

Comentar

Realizar consulta tres tablas, una tabla contiene "opciones"

guardar coordenadas GPS