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?

Answer 1

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.

Answer 2

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

Answer 3

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