MySQL – Case Statements for Multiple Fields with Specific Cases

caseMySQL

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.

Best Answer

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

Related Question