Not your average IF-statement...

[Borje]

... Or least I suppose it's not

I currently have the following update with an if statement:

UPDATE table
SET column1 = column1 + IF((@gain := FLOOR((value1 - column2) * value2)) AND @gain >= 1,
    IF(@gain + column1 > value3, value3, @gain + column1), 0)
WHERE id = 1

And it works fine (I realize that with FLOOR() is stupid to check if @gain >= 1, explaination follows). But I want to update column2 too, but only if column1 is updated, and I like to do it all in one statement without locking the table. I want to do something like this:

UPDATE table
SET column1 = column1 +
   IF((@gain := FLOOR((value1 - column2) * value2)) AND @gain >= 1, (@var := value1) AND
      IF(@gain + column1 > value3, value3, @gain + column1), (@var := column2) AND 0),
      column2 = @var
WHERE id = 1

In the last query column2 is updated correctly, but column1 is always only increased by 1, which isn't so strange since the AND operator would do that with two statements that assert true. But how should I do to achieve wanted result?

[Borje]

No worries, I managed to solve it using the following:

UPDATE building_factories
SET column1 = LEAST((@gain := FLOOR((UNIX_TIMESTAMP() - column2) * (production_per_hour / 3600)) + column1, value1),
column2 = IF(@gain >= 1, column2 + (@gain * 3600/production_per_hour), column2)
WHERE id = 1

As you might understand column2 was just a timestamp to keep track on when I last updated column1 which was just showing a stock (with a limitation to a max value). This query was to simulate production of goods at a certain production/hour rate.