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.
(bill_id, stockid)
, I don't see a problem then.#WHERE
bill_id` = 1`, without mentioning the stockid in the WHERE.