CODEWITHSUNDEEP
sqlsql-server
Barlet
Barlet

Reputation: 555

SQL Update with results from another Table

I want to update the qty field on my table products with the following query. This query is returning me the desired numbers in Qty field. 

SELECT e.Prod_name, e.Prod_number,
  (SUM(e.Qty) - SUM(g.Qty)) Qty
FROM Products_Invoices e JOIN (
    SELECT product_number, SUM(qty) Qty
    FROM SoldItemsCalc
    GROUP BY product_number
  ) g ON e.Prod_number = g.product_number
GROUP BY Prod_number, Prod_name
order by Prod_name

How can I use this query and update the values where the product_number matches

I don't mind if you change the query... Just need to update the qty values

Thank you

Upvotes: 3

Views: 1168

Answers (2)

sgeddes
sgeddes

Reputation: 62861

Are you sure your qty is producing your desired results? You're doing a SUM in your subquery and then doing another SUM in your outer query. That is probably messing your numbers up. Here is a slight variation of @Gordon's good answer -- this is updating the Product table using the qty I think you want:

with toupdate as (
    SELECT e.Prod_name, e.Prod_number, (SUM(e.Qty) - g.Qty) Qty
    FROM Products_Invoices e JOIN (
         SELECT product_number, SUM(qty) Qty
         FROM SoldItemsCalc
         GROUP BY product_number
        ) g ON e.Prod_number = g.product_number
    GROUP BY Prod_number, Prod_name, g.Qty
   )
update p
    set qty = t.qty
    from products p join toupdate t on p.prod_number = t.prod_number;

And here is the Fiddle with the example: http://www.sqlfiddle.com/#!3/42c80/1

There are 2 records in the Product_Invoices table which SUM to 150. And 2 records in the SoldItemsCalc table which sum to 25. Using your query, the overall result changes to 100 when I imagine you want 125.

BTW -- using the CTE above will work for SQL Server 2005 and higher. If you need to support SQL Server 2000, then use this version:

update p
    set qty = t.qty
    from products p join (
      SELECT e.Prod_name, e.Prod_number, (SUM(e.Qty) - g.Qty) Qty
      FROM Products_Invoices e JOIN (
         SELECT product_number, SUM(qty) Qty
         FROM SoldItemsCalc
         GROUP BY product_number
        ) g ON e.Prod_number = g.product_number
    GROUP BY Prod_number, Prod_name, g.Qty) t on p.prod_number = t.prod_number;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I do it this way:

with toupdate as (
    SELECT e.Prod_name, e.Prod_number, (SUM(e.Qty) - SUM(g.Qty)) Qty
    FROM Products_Invoices e JOIN (
         SELECT product_number, SUM(qty) Qty
         FROM SoldItemsCalc
         GROUP BY product_number
        ) g ON e.Prod_number = g.product_number
    GROUP BY Prod_number, Prod_name
   )
update t
    set qty = toupdate.qty
    from toupdate
    where t.prod_nubmer = toupdate.prod_number

Upvotes: 3

Related Questions