Imagine, we store bit-flags in a database as integers and want to update the bit-set by simultaneously deleting and updating the bits. An expression like this can quickly become unhandy. However, I wanted to make this solution pretty and after some tinkering I came up with a quite nice solution.
Let's say, you want to formulate a MySQL query like this:
UPDATE t1 SET flag=(SET 3. bit, DELETE 4. BIT) WHERE id=5;
(TL;DR: The finaly copy and paste solution is at the end.) You want all the remaining flags untouched and only 3 and 4 to be modified if necessary. You could do a lookup-table for this, but by looking more closely, you'll recognize, that the old flags should be used if a mask is pointing positively to bit 3 and negatively to bit 4. This will give us the following expression:
\[(\text{flag} \& \text{~}\text{mask}) | (\text{new} \& \text{mask})\]
But what is the mask and what is new?
\[\text{mask} = \sum\limits_{i=0}^N 2^{x_i} = \bigvee\limits_{i=0}^N 1 << x_i\]
\[\text{new} = \sum\limits_{i=0}^N d_i 2^{x_i} = \bigvee\limits_{i=0}^N d_i << x_i\]
With \(x_i\) being the index to be changed and \(d_i\) the appropriate value, either zero or one which should be set. Now the interesting part begins: When you see \((new \& mask)\), you see that the initial formula wasn't minimal enough. But let's proof a simpler expression with a lookup table:
| flag | new | mask | (flag&~mask)|(new&mask) | (flag&~mask)|new |
|---|---|---|---|---|
| 0 | 0 | 0 | 0 | 0 |
| 0 | 0 | 1 | 0 | 0 |
| 0 | 1 | 0 | 0 | 1 |
| 0 | 1 | 1 | 1 | 1 |
| 1 | 0 | 0 | 1 | 1 |
| 1 | 0 | 1 | 0 | 0 |
| 1 | 1 | 0 | 1 | 1 |
| 1 | 1 | 1 | 1 | 1 |
Now something really beautiful is possible when you think about it: The mask is made of all bits we need to change and as such, we first delete it and add it again afterwards, which is a redundant step. This means, we can simplify the formula and separate the delete and set-step. So the one contradiction of the proof above becomes a don't-care. More formally we get:
\[\text{flag}'=(\text{flag} \& ~D) | S\]
With \(D\) and \(S\) being the sets to be deleted and set. Putting it together we get a really nice SQL statement, you'll need to run:
UPDATE t1 SET flag=flag & ~16 | 8 WHERE id=5;
In a real world application you would define some constants and calculate the values in the application:
<?php
define('F_FOO1', 1 << 0);
define('F_FOO2', 1 << 1);
define('F_FOO3', 1 << 2);
define('F_FOO4', 1 << 3);
// Delete F_FOO2 and F_FOO3, but set F_FOO4 and F_FOO1:
$S = (F_FOO1 | F_FOO4);
$D = (F_FOO2 | F_FOO3);
mysql_query("UPDATE t1 SET flag=flag & ~" . $D . " | " . $S . " WHERE id=" . $id);
I really like this result. And thinking directly about the solution also makes totally sense.