SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    Helsingborg, Sweden
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Not your average IF-statement...

    ... Or least I suppose it's not

    I currently have the following update with an if statement:
    Code:
    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:

    Code:
    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?

  2. #2
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    Helsingborg, Sweden
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No worries, I managed to solve it using the following:

    Code:
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •