sql - 将 COUNT 的值添加到现有值

Question

我有一个管理库存变动的表和一个管理库存的表。
我想将项目在给定日期之间存在的移动计数添加到库存表中。
我的更新语句如下所示:

UPDATE inventory 
SET    quantity = quantity + 1
WHERE  ItemID IN 
       ( SELECT ItemID FROM movements
         WHERE  group = '3' AND store = '500'
         AND    DateMove BETWEEN 20201219 AND 20201223 )
AND    StoreNumber = '500'

如何更改此查询以将 ItemID 在移动中出现的次数添加到库存表中的数量？
我一直在想我可以添加一个 count(itemID) 和 group by 并在子查询中添加一个别名并在 + 之后使用别名，但它似乎不起作用。
谢谢你的帮助

Answer 1

使用 UPDATE ，您可以使用相关子查询:

UPDATE inventory i
    SET quantity = (i.quantity +
                    (SELECT COUNT(*)
                     FROM movements m
                     WHERE m.ItemId = i.ItemId AND
                           m.group = 3 AND m.store = i.store AND
                           m.DateMove BETWEEN 20201219 AND 20201223 
                    )
                   )
WHERE i.store = 500 AND
      EXISTS (SELECT 1
              FROM movements m
              WHERE m.ItemId = i.ItemId AND
                    m.group = 3 AND m.store = 500 AND
                    m.DateMove BETWEEN 20201219 AND 20201223 
             );

请注意，我删除了 500 周围的单引号和 3 .这些值看起来像数字。仅当它们是字符串时才使用单引号。
Oracle 还允许您在某些情况下使用子查询进行更新，因此这也应该有效:

update (select i.*,
               (SELECT COUNT(*)
                FROM movements m
                WHERE m.ItemId = i.ItemId AND
                      m.group = 3 AND m.store = i.store AND
                      m.DateMove BETWEEN 20201219 AND 20201223 
               ) as inc_quantity
       from inventory i
       where store = 500
      )
    set quantity = quantity + inc_quantity
    where quantity > 0;