Update add product from one table to another table

Asked

Viewed 125 times

-1

The query below it takes the product from the SALES table where it is equal to the product from the STOCK table and sum the quantity, it occurs that this query only works if it has 1 sales line/product, I would like to make it go through as many lines as the SALE has and not only one, I don’t know if you need to wear while or go.

UPDATE PRODUTOS  SET ESTOQUE = ESTOQUE +  ( SELECT QUANTIDADE FROM VENDAS WHERE CODIGO=' " + Convert.ToInt32(codigoTextBox.Text) + " ' ) WHERE  Codigo = ( SELECT CodigoProduto FROM VENDAS WHERE CODIGO= ' " + Convert.ToInt32(codigoTextBox.Text) + "' )";

Could you help me? Thank you.

sql

  • Welcome to Stackoverflow. Edit your code to put the whole code in code format. You can select the code and click on the option { }

  • the problem of "only works with one line" could solve by placing a sum in the sub-query: SELECT sum(QUANTIDADE) FROM VENDAS WHERE CODIGO... but you have to see what each "code" of this one means, the second sub-query can be improved, but you have to know what the logic

1 answer

0

Please try this way and see if it resolves for you:

UPDATE PRODUTOS
SET ESTOQUE = ESTOQUE + V.QUANTIDADE
FROM PRODUTOS P
INNER JOIN (
    SELECT CodigoProduto, SUM(QUANTIDADE) AS QUANTIDADE
    FROM VENDAS GROUP BY CodigoProduto) as V ON V.CodigoProduto = P.CodigoProduto
WHERE V.CodigoProduto = ' " + Convert.ToInt32(codigoTextBox.Text) + " ';

  • Thanks @Ndelavi, it worked just made a few changes below and it worked. UPDATE PRODUTOS
SET ESTOQUE = ESTOQUE + V.QUANTIDADE
FROM PRODUTOS P
INNER JOIN (
 SELECT CODIGO,CodigoProduto, SUM(QUANTIDADE) AS QUANTIDADE
 FROM VENDAS GROUP BY CODIGO,CodigoProduto) as V ON V.CodigoProduto = P.Codigo
WHERE V.CODIGO = 382

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.