# Mysql – Case statements for multiple fields when only certain cases exist for each field

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.

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