CODEWITHSUNDEEP
mysqlcase
j10
j10

Reputation: 2261

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

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.

Upvotes: 0

Views: 34

Answers (1)

Barmar
Barmar

Reputation: 781068

What you've written should work, but it will get very complex if you have to update different columns for many different stock IDs. It would probably be simpler, and maybe better performance, to do a separate query for each ID.

BEGIN TRANSACTION;
UPDATE billlineitem 
    SET rate = '40', `updated_billitemquantity_at` = '2019-09-06 05:16:06.219' 
    WHERE stockid = 10;
UPDATE billlineitem 
    SET qty = 5, `updated_billitemquantity_at` = '2019-09-06 05:16:06.219'
    WHERE stockid = 11;
COMMIT;

Upvotes: 3

Related Questions