Warning: WP_Syntax::substituteToken(): Argument #1 ($match) must be passed by reference, value given in /home/www/jaran.de/goodbits/wp-content/plugins/wp-syntax/wp-syntax.php on line 380
The problem with blogging solutions specifically for one person is that he comes back and asks for more 😉 Luckily this time it’s easier!
What we got: a simple table with one column whose values describe a difference of some kind. That is we got one column called difference, that has negative or positive (or 0) numbers.
What we want: we’re looking for the most simple and performant way to update two other columns from that difference one: if the difference was positive, this value is to be updated into a gain column. If it is negative, then the positive value (the absolute value) is to be updated into the loss column.
So an initial table (named differences) might look like this:
| difference | gain | loss |
|---|---|---|
| -13 | 0 | 0 |
| 27 | 0 | 0 |
| 2 | 0 | 0 |
| 0 | 0 | 0 |
| -3 | 0 | 0 |
| 16 | 0 | 0 |
The update query is quite simple and uses the conditional if-function:
UPDATE differences SET gain = IF(difference > 0, difference, 0), loss = IF(difference < 0, -difference, 0) |
And the result:
| difference | gain | loss |
|---|---|---|
| -13 | 0 | 13 |
| 27 | 27 | 0 |
| 2 | 2 | 0 |
| 0 | 0 | 0 |
| -3 | 0 | 3 |
| 16 | 16 | 0 |
I expect the first commenter to headslap himself 😉
Again the Jaran blog to the rescue, I’ve always done simple equations but the exact syntax for deltas and the default value in the end of the statement wasn’t clear in the documentation I found and he rescued me again.
As obligated… *facepalm*