Adding value of COUNT to an already existing value

user_14895566 0
Viewed 93

I have a table for managing inventory movements and a table to manage stock. I want to add the count of movements that the item exists in between given dates to the stock table.

My update statement looks like this:

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'

How can I change this query to add the amount of times that the ItemID appears in movements to the quantity in the inventory table?

I've been thinking that I can add a count(itemID) and group by and add an alias in the subquery and use the alias after the + but it doesn't seem to work.

Thanks for any help

oracle sql
4 Answers

You appear to want a MERGE statement and to COUNT the movements:

MERGE INTO inventory dst
USING (
  SELECT ItemID,
         store,
         COUNT(*) AS num_movements
  FROM   movements
  WHERE  group = 3
  AND    store = 500
  AND    DateMove BETWEEN DATE '2020-12-19' AND DATE '2020-12-23'
  GROUP BY
         ItemId,
         store
) src
ON ( src.ItemId = dst.ItemId AND dst.StoreNumber = src.store )
WHEN MATCHED THEN
  UPDATE
  SET quantity = dst.quantity + src.num_movements;

(Also, if values are numbers then use number literals and not string literals and, for dates, use date literals and not numbers.)

user_15092640

Using an UPDATE, you can use a correlated subquery:

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 
             );

Note that I removed the single quotes around 500 and 3. These values look like numbers. Only use the single quotes if they are strings.

Oracle also allows you to update using a subquery under some circumstances, so this should work as well:

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;
user_11440350

You need a correlated subquery. For brevity, I've omitted all other where conditions.

UPDATE inventory AS inv
SET quantity = quantity + (SELECT COUNT(*) FROM movements AS mov WHERE mov.itemID = inv.itemID);
user_115796040

I think it's better to create a view such as

CREATE OR REPLACE VIEW v_inventory AS
SELECT i.*,
       SUM(CASE
           WHEN i.StoreNumber = 500 AND m."group" = 3 AND m.store = 500 AND
                m.DateMove BETWEEN date'2020-12-19' AND date'2020-12-23' 
           THEN
                1
           ELSE
                0
            END) OVER (PARTITION BY m.ItemID) AS mov_quantity
  FROM inventory i
  LEFT JOIN movements m
    ON m.ItemID = i.ItemID

rather than applying a DML for the sake of good db design, since the desired count already can be calculated, and may yield for later confusions

user_58413060
Related