How to update 2 tables row by row sequentially in SQL

Question

I need to update two table in parallel based on each other data.

Below is the table structure for both tables.

Product table:

ProductId | ProductQuantity | Min Price | Max Price    
----------+-----------------+-----------+----------
1         | 122             | 58        | 585    
2         | 548             | 45        | 856

Order table:

Order ID | ProductID | ProductOrderQuantity |OfferPrice | OrderProcessDate | Status     
---------+-----------+----------------------+-----------+------------------+--------
1        | 2         |  35                  |  75       | Null             | Placed    
2        | 1         | 752                  | 258       | Null             | Placed

I need to update Status from Order table to "Confirm/Reject" in below condition:

ProductOrderQuantity should be less than ProductQuantity in the Product table
OfferPrice should be in between Min Price and Max Price
If both conditions match, then update Status in the Order table to Confirm/Reject and OrderProcessDate as the date on which it processed

This update should be done sequentially to each row from order table and once status from a row updates to "confirmed" immediately update ProductQuantity as ProductQuantity - ProductOrderQuantity

Forget about sequential / parallel concepts. If different records need to be consistent at the same time, you need to use a transaction. — Nick.Mc, Aug 24 '19 at 08:33
Yes, But I need first row from order table should update then Product table. After this execution should come to second row from order table, then product table and it keeps going till last row of order table. — Mahendra Upadhyay, Aug 24 '19 at 09:37
I'd recommend to search for "use table as a queue". https://stackoverflow.com/questions/2177880/using-a-database-table-as-a-queue https://rusanu.com/2010/03/26/using-tables-as-queues/ https://dba.stackexchange.com/questions/20399/fifo-queue-table-for-multiple-workers-in-sql-server — Vladimir Baranov, Aug 24 '19 at 12:04
You need to have a column that designates 'order'. At this stage it looks like `OrderId` right? — Nick.Mc, Aug 24 '19 at 12:11

ScaisEdge · Answer 1 · 2019-08-24T09:39:56.897

1

You could check using

select 'OK'
FROM Order
INNER JOIN Product ON Product.ProductId = Order.ProductId
WHERE order.ProductOrderQuantity < Product.ProductQuantity
  AND order.OfferPrice between Product.min_price and Product.Max_price

And eventually You could try using a update in JOIN for check the condition

update Order 
SET status = "Confirm/Reject",
    OrderProcessDate =  GETDATE()
FROM Order
INNER JOIN Product ON Product.ProductId = Order.ProductId
WHERE order.ProductOrderQuantity < Product.ProductQuantity
  AND order.OfferPrice between Product.min_price and Product.Max_price

edited Aug 24 '19 at 09:39

answered Aug 24 '19 at 08:07

ScaisEdge

131,976
10
91
107

1

Thanks scaisEdge. In above query how Product Table will update? Condition define in last line of my question. – Mahendra Upadhyay Aug 24 '19 at 09:34
If you set an order id adding a where condition the updated should involve all the rows with valid condition for the order.. – ScaisEdge Aug 24 '19 at 09:39

score 0 · Answer 2 · answered Aug 24 '19 at 13:44

I assume that the orders are sorted by OrderId. That means, the lower the order, the higher the priority.

See the following code, I think it will help you

CREATE TABLE #OrderJobDispatch(
  OrderId BIGINT
)

DECLARE @orderID BIGINT;

INSERT INTO #OrderJobDispatch (OrderId)
SELECT OrderId
FROM
  [Order]
WHERE
  Status = 'Placed'

WHILE EXISTS(SELECT * FROM #OrderJobDispatch)
BEGIN
  -- take the highest priority order
  SET @orderId = SELECT TOP(1) OrderId FROM #OrderJobDispatch ORDER BY OrderId

  -- update Order table if the condition matches
  UPDATE o
  SET o.Status = 'Confirm/Reject'
  FROM
    [Order] AS o
  INNER JOIN
    [Product] AS p
  ON
    o.ProductId = p.ProductId
  WHERE
    o.OrderId = @orderId
  AND
    o.ProductOrderQuantity < p.ProductQuantity
  AND 
    o.OfferPrice BETWEEN p.MinPrice AND p.MaxPrice

  -- update Product table if it is appropriate
  UPDATE p
  SET ProductQuantity = p.ProductQuantity - o.ProductOrderQuantity 
  FROM 
    [Product] AS p
  INNER JOIN
    [Order] AS o
  ON
    p.ProductId = o.ProductId
  WHERE
    o.OrderId = @orderId
  AND
    o.Status = 'Confirm/Reject'

  DELETE FROM #OrderJobDispatch
  WHERE OrderId = @orderId
END

Here, we are actually using #OrderJobDispatch table like a Queue. While there is a single row in that queue-like table, we are doing operations using OrderId.

score 0 · Accepted Answer · answered Aug 24 '19 at 16:11

I was not able to test the code but the steps should be clear:

while 1 = 1
begin
  declare @OrderId int

  begin transaction

  select @OrderId = min(OrderId)
  from Order
  where Status = "Placed"

  if @@rowcount = 0
  begin
    /* Done with processing all orders */
    commit transaction
    break
  end

  update Order
  set Status = "Confirm"
  from Order o
  inner join Product p
  on p.ProductId = o.ProductId
  where o.OrderId = @OrderId
  and o.ProductOrderQuantity < p.ProductQuantity
  and o.OfferPrice between p.MinPrice and p.MaxPrice

  if @@rowcount = 1
  begin
    update Product
    set ProductQuantity = p.ProductQuantity - o.ProductOrderQuantity
    from Product p
    inner join Order o
    on p.ProductId = o.ProductId
    where o.OrderId = @OrderId

    commit transaction
  end
  else
  begin
    update Order
    set Status = "Reject"
    from Order o
    inner join Product p
    on p.ProductId = o.ProductId
    where o.OrderId = @OrderId

    commit transaction
  end
end

How to update 2 tables row by row sequentially in SQL

3 Answers3