MySQL update column which is a value in another column

Pankaj Singhal Published at Dev

Pankaj Singhal

This is my previous question related to the my query. MySQL select column which is a value in another column

The problem is that want to do operations on the values extracted and store it back into the original db. I've tried using a update & case but am not able to achieve it.

update msisdn_table
   CASE reason
     WHEN 'NoAnswer' THEN (case when (NoAnswer>0) then update msisdn_table set NoAnswer = NoAnswer-1 end)
     WHEN 'NetworkBusy' THEN (case when NetworkBusy>0 then update msisdn_table set NetworkBusy = NetworkBusy-1 end)
     WHEN 'CallRejection' THEN (case when CallRejection>0 then update msisdn_table set CallRejection = CallRejection-1 end)
     WHEN 'Unavailable' THEN (case when Unavailable>0 then update msisdn_table set Unavailable = Unavailable-1 end)
   END

Any help?

peterm

Try it this way if you want to do it one statement

UPDATE msisdn_table
   SET NoAnswer      = IFNULL(IF(reason = 'NoAnswer',      
                                 NULLIF(NoAnswer,      0) - 1, NoAnswer), 0), 
       NetworkBusy   = IFNULL(IF(reason = 'NetworkBusy',   
                                 NULLIF(NetworkBusy,   0) - 1, NetworkBusy), 0), 
       CallRejection = IFNULL(IF(reason = 'CallRejection', 
                                 NULLIF(CallRejection, 0) - 1, CallRejection), 0), 
       Unavailable   = IFNULL(IF(reason = 'Unavailable',   
                                 NULLIF(Unavailable,   0) - 1, Unavailable), 0)
  WHERE reason IN('NoAnswer', 'NetworkBusy', 'CallRejection', 'Unavailable');

Note:

I changed CASE with less verbose IF(), although if you like it better you can use it the same way.
This approach has one possible side effect as it always updates the column(s) either with a new or with old value. It may matter if for example you have a trigger defined on the table.
You want to apply a WHERE clause to make sure that rows with other reason codes are not affected

Here is SQLFiddle demo

Collected from the Internet

Please contact [email protected] to delete if infringement.