另一个表格的结果更新SQL语句

Question

我想使用以下查询更新我的产品表中的数量字段。该查询将返回我所需的数量。

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

我该如何使用这个查询并更新与产品编号匹配的值？

如果需要更改查询，我不介意...只需要更新qty值。

谢谢

- Barlet

Answer 1

我是这样做的：

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

- Gordon Linoff

Answer 2

你确定你的数量产生了你想要的结果吗？在子查询中进行了SUM，然后在外部查询中再次进行SUM。这可能会使你的数字出错。这是对@Gordon的好答案的轻微变化--这将使用我认为你想要的数量更新Product表：

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;

这是一个示例Fiddle：http://www.sqlfiddle.com/#!3/42c80/1

Product_Invoices表中有2条记录，总和为150。SoldItemsCalc表中有2条记录，总和为25。使用您的查询，当我想要125时，总体结果会变为100。

顺便说一句 - 使用上面的CTE将适用于SQL Server 2005及更高版本。如果需要支持SQL Server 2000，则使用此版本：

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;

- sgeddes

---

我也用你的查询得到了相同的结果... 我仔细检查了我的结果，它们似乎没问题...谢谢。 - Barlet

@Barlet -- 好的，但要小心 -- 参见这个fiddle以了解我的意思：http://www.sqlfiddle.com/#!3/0cc74/1 -- 祝你好运！ - sgeddes

@Barlet -- 基本上，如果您的Products_Invoices表中有多个具有相同prod_number的记录，则您的Summed SoldItemsCalc表将是错误的（乘以另一个表中记录数的乘数）。 如果您的查询返回所需内容，则不必担心 - 只是想让您知道 :) - sgeddes