1

We have an inventory feature where we generate Bills. There is an Edit Bill API call. We have implemented it as PATCH call.

A Bill with id = 1 has 2 LineItems :

|  Stock Id |   Qty        |  Rate       |
|    10     |      2       |    10       |
|    11     |      3       |    20       |

Now lets say I want to change the quantity for stock Id : 10 to 5 and I want to change the rate for stock Id : 11 to 40

We have represented it as PATCH Call :

bill : {
    id : 1

    lineItems : [
    {
        stockId : 10,
        qty : 5,
     },

     {
        stockId : 11,
        rate : 40   
     }
    ]
    }

In the backend we run following query :

 UPDATE `billlineitem` 
SET    `rate` = ( CASE 
                    WHEN stockid = 11 THEN '40' 
                    ELSE rate 
                  END ), 
       `qty` = ( CASE 
                   WHEN stockid = 10 THEN 5 
                   ELSE qty 
                 END ), 
       `updated_billitemquantity_at` = '2019-09-06 05:16:06.219' 
WHERE  `bill_id` = '1' 
       AND `stockid` IN ( 10, 11 ) 

Is it ok, in the above case when there is no change for an attribute then the else clause will take the value from the database for that attribute. The above update statement is run in a transaction.

Is this a correct approach? Will this do an update for every attribute for every stock Id. Is there a better approach?

We are using MySQL DB.

Apart from qty and rate we also have discountType, discountValue and there are other 4 more attributes.

7
  • Seems ok. The other approach is to split into 2 updates. But I don't think there would be much difference. Sep 6, 2019 at 7:17
  • my bad. I forgot to mention that there are 6-7 such attributes. I just updated the question.
    – j10
    Sep 6, 2019 at 7:28
  • Not much different with 7 attributes. The question is how many rows are updated? Sep 6, 2019 at 7:50
  • 1
    Assuming there is a (UNIQUE) index on (bill_id, stockid), I don't see a problem then.# Sep 6, 2019 at 8:02
  • 1
    If an UPDATE updates all rows with the same bill_id, then you could also just use WHERE bill_id` = 1`, without mentioning the stockid in the WHERE. Sep 6, 2019 at 8:05

1 Answer 1

2

The single-UPDATE you have seems reasonable. However, depending on statistics and how well the Optimizer handles IN, this 2-UPDATE approach may be faster.

Either will benefit from

INDEX(bill_id, stockid)

The order of the columns in the index matters. bill_id needs to be first for the single-update with the CASE statement. Doing multiple UPDATEs, as follows, would not care which order the INDEX is written.

UPDATE `billlineitem` 
    SET    `rate` =  '40',
           `updated_billitemquantity_at` = '2019-09-06 05:16:06.219' 
    WHERE bill_id = 1
      AND stockid = 11;
UPDATE `billlineitem` 
    SET    `rate` =  '5',
           `updated_billitemquantity_at` = '2019-09-06 05:16:06.219' 
    WHERE bill_id = 1
      AND stockid = 10;

(For updating 15 rows, there would need to be 15 UPDATE statements; this may be impractical.)

5
  • Thank you Rick. (The order of the columns matters for the 1- update version.) --> Is this for our current approach ? You mean the order of columns to be updated or in the where clause ? Also, in your solution if there are 15 items to be updated --> we will have 15 UPDATES. Will that be any problem ?
    – j10
    Sep 14, 2019 at 6:34
  • @j10 - Sorry, I was a bit vague. Hopefully, I have clarified that.
    – Rick James
    Sep 14, 2019 at 15:05
  • Thank you Rick. That was quite helpful. so the CASE one will be ok for 15 rows? since you mentioned that 15 update Statements may be impratical.
    – j10
    Sep 15, 2019 at 6:53
  • @Rick_James: slightly confused here: does each CASE Statement condition need to have bill_Id?
    – j10
    Sep 15, 2019 at 7:01
  • @j10 - Your UPDATE with 2 CASEs looked fine. bill_id was tested in the WHERE where it would be the same for all 15 cases? Then each CASE has the rest of the comparisons.
    – Rick James
    Sep 15, 2019 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.