SQL Server：根据值的选择使用同一个表中的值更新列

I have a table that contains inventory items, where the same item can be stored in multiple places (i.e. Total qty=3, 1 stored in cabinet, and 2 in the fridge, and 0 in the pantry). Each of these have an entry type of "20".

我有一个包含库存物品的表，其中相同的物品可以存储在多个地方（即总数量= 3,1存储在橱柜中，2在冰箱中，0在食品室中）。它们中的每一个都具有“20”的条目类型。

There is also rows with entry type of "10" that control the totals. So, in my program I see "Available" as Entry Type "10", and can view each locations quantities by viewing the Entry Type "20".

还有一些条目类型为“10”的行控制总计。因此，在我的程序中，我将“可用”视为条目类型“10”，并且可以通过查看条目类型“20”查看每个位置数量。

After running some updates, I noticed that it took my "10" types to zero even if there were items in the "20" rows (on a certain set of items, which I have a list of to include within an "IN" statement to change only those rows)

运行一些更新之后，我注意到即使“20”行中有项目，我的“10”类型也为零（在某一组项目上，我有一个列表包含在“IN”语句中）只更改那些行）

I am looking to update that table with some sort of CASE statement (I think) to force "10" entry to be equal to the SUM of the "20" entries.

我希望用某种CASE语句（我认为）来更新该表，以强制“10”条目等于“20”条目的SUM。

Table looks like this:

表看起来像这样：

ITEM_ID    ENTRYTYPE    QTY       LOCATION_ID
------------------------------------------------
  A01        10          0       ALL-AVAILABLE   <--THIS ROW SHOULD SHOW QTY=3
  A01        20          1          CABINET
  A01        20          2          FRIDGE
  A01        20          0          PANTRY
  A02        10          0       ALL-AVAILABLE   <--THIS ROW SHOULD SHOW QTY=6
  A02        20          2          CABINET
  A02        20          2          FRIDGE
  A02        20          2          PANTRY

I think the code is basically something like:

我认为代码基本上是这样的：

UPDATE Inventory 
SET qty = SUM(t2.qty)
FROM Inventory t2
WHERE (ENTRYTYPE = 10 AND qty = 0) 
  AND ITEM_ID IN (list already generated to be pasted later);

Of course, I couldn't make this work for whatever reason. All help appreciated!

当然，无论出于何种原因，我都无法做到这一点。所有帮助赞赏！

TIA

3 个解决方案

#1

You can use a correlated update.

您可以使用相关更新。

UPDATE t2
SET qty = (select SUM(qty) from Inventory where item_id=t2.item_id and ENTRYTYPE = 20)
FROM Inventory t2
where ENTRYTYPE=10 AND qty=0

To restrict the item_id's that get updated, use a where condition.

要限制更新的item_id，请使用where条件。

#2

Assuming that ENTRYTYPE = 10 is always the record you want to have the total quantity updated in:

假设ENTRYTYPE = 10始终是您希望更新总数量的记录：

UPDATE Inventory 
SET qty = (SELECT SUM(qty) FROM Inventory WHERE ENTRYTYPE = 20)
WHERE ENTRYTYPE = 10 AND qty = 0;

#3

I would do this using an updatable CTE:

我会使用可更新的CTE来做到这一点：

with toupdate as (
      select i.*, sum(qty) over (partition by item_id) as sumqty
      from inventory i
     )
update toupdate
    set qty = sumqty
    where entrytype = 20;

#1