Tutorial :MySQL status swap for bitwise operators



Question:

I am looking for a solution/best practice to do a swap of values for a status flag.

The status INT(3) in mysql has a few values, and I'd like to be able to swap the LIVE and NOT_LIVE values around, without interrupting what other bitwise values are in there.

If it was a flag field, as in 0 or 1, it is easy:

'status' NOT 'status'  

I was wondering if there was a way I could do a swap based on two values (x, y) - without too much code-logic, like the code above. Of course I may be dreaming, and just have to revert to a SELECT query, and if statement and an UPDATE...

any ideas?


Solution:1

so your values are 1 and 2, but you want bit-style flipping? that won't work because 1 and 2 have different bits set!

try this:

UPDATE table SET status = CASE status WHEN 1 THEN 2 ELSE 1 END  


Solution:2

What do you mean exactly by "swap[ping] the LIVE and NOT_LIVE values" ? If LIVE is a bit that is set/unset then what you are looking for is a bitwise NAND. For instance, if its value is 8 and you want to unset it

UPDATE table SET status = status &~ 8  

It will unset the bit whose value is 8. If LIVE and NOT_LIVE are two different bits then you'll need to set one (OR) and unset the other (NAND). Here I'll remove NOT_LIVE (4) while setting LIVE (8)

UPDATE table SET status = (status &~ 4) | 8  

If status can only have two values then what you need is add those two values then substract status. For instance, if the two values are 1 and 2

UPDATE table SET status = (1 + 2) - status  

which of course you can write

UPDATE table SET status = 3 - status  

Note that this will fail if status takes any other value. You can avoid that by only changing status if it's one of the two.

UPDATE table SET status = 3 - status WHERE status IN (1, 2)  

In this case, if status is neither LIVE or NOT_LIVE, it won't be changed.


Solution:3

You need the case statement here:

UPDATE table SET status = CASE      WHEN status = 1 THEN 2      WHEN status = 2 THEN 3      ELSE 1  END CASE  

Alternately, you can use the if statement, if you like:

UPDATE table SET status =      IF     status = 1 THEN 2      ELSEIF status = 2 THEN 3      ELSE   1  END IF  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »