0

As the question-title says i am searching for a way to achieve this in only one sql-call:

UPDATE accountEnergy SET value=LEAST(value+$energyChange, maxValue)

My solution with two calls:

mysqli_query($db, "UPDATE accountEnergy SET value=value+$energyChange WHERE accountId=$accountId");
mysqli_query($db, "UPDATE accountEnergy SET value=maxValue WHERE accountId=$accountId AND value>maxValue");
Marcel Ennix
  • 1,328
  • 1
  • 12
  • 16

1 Answers1

2

I would put this application logic into your application, not into the database (query).

That said, you can solve this with a sub-query:

UPDATE yourTable
SET yourValue = (
    SELECT LEAST(yourValue + $change, yourMax)
    FROM yourTable
    WHERE yourId = $yourId
)
WHERE yourId = $yourId

Edit: It appears that MySQL does not allow you to reference the table you are updating also in a sub-query.

A solution to this problem is shown in this StackOverflow answer.

Community
  • 1
  • 1
TimoStaudinger
  • 41,396
  • 16
  • 88
  • 94
  • value and maxValue are registered words from sql, putting them into `` solved it UPDATE w1_account_energy SET `value` = ( SELECT LEAST(`value`+10, `maxValue`) FROM w1_account_energy WHERE accountId = 1 ) WHERE accountId = 1 throws now: #1093 - You can't specify target table 'w1_account_energy' for update in FROM clause – Marcel Ennix Jun 18 '16 at 17:55
  • Is it solved now or not? Not sure how to read your edited comment. – TimoStaudinger Jun 18 '16 at 18:01
  • Sorry. NOT solved, because of the "can't specify target table for update in FROM clause". maybe i have to use a temp table to get it in one call. – Marcel Ennix Jun 18 '16 at 18:04
  • 1
    Have a look at my edit. This seems to be an issue with MySQL that can be solved. – TimoStaudinger Jun 18 '16 at 18:13
  • do it with an "Update with a Self-join" pattern, not a temp-table. Listen to Timo – Drew Jun 18 '16 at 19:39