0
Follow
0
View

How to incr/decr column values based on passed parameters?

asdw7540522 注册会员
2023-01-25 15:58

What you are actually trying to do here is a merge.

  • Construct a virtual source table of changes: the From store gets a negative quantity.
  • Merge that into the existing table, inserting in cases where there is no row for that location and movie combination
CREATE OR ALTER PROCEDURE MoveStock
    @MovieID int,
    @StoreID_From int,
    @StoreID_To int,
    @StockToMove int = 1;
AS

WITH Source AS (
    SELECT StoreID = @StoreID_To, DiffQty = @StockToMove
    UNION ALL
    SELECT @StoreID_From, (-@StockToMove)
),
Target AS (
    SELECT *
    FROM Table1
    WHERE MovieID = @MovieID
      AND StoreID IN (@StoreID_From, @StoreID_To)  -- this line for performance only, try without
)
MERGE Target t
USING Source s
   ON s.StoreID = t.StoreID
WHEN MATCHED AND t.stock + s.DiffQty <= 0
    THEN DELETE  -- decide whether you want this condition
WHEN MATCHED THEN
    UPDATE SET Movies_in_stock += s.DiffQty
WHEN NOT MATCHED THEN
    INSERT (StoreID, MovieID, Movies_in_stock)
    VALUES (S.StoreID, @MovieID, s.DifQty)
;