How to use temporary variable in Update query?

How can I set a variable while doing an Update statement?

I can't seem to figure out the syntax. I am using this @tp variable for holding calculated result and it will be used in when condition.

I want something like this:

UPDATE users IU
INNER JOIN activity_stats DAS
    ON (IU.user_id=DAS.for_user_id) SET 
    @tp:= IU.total_subscription_time - (unix_timestamp()- IU.start_date),
    IU.time_period_left = CASE WHEN @tp > 0 THEN @tp ELSE 0 END,
    IU.used_time = (unix_timestamp() - IU.start_date),
    IU.status = CASE WHEN @tp > 0 THEN 'A' ELSE 'I' END,
    DAS.total_time_used = DAS.total_time_used + IU.used_time 
WHERE IU.time_period_left > 0
  AND IU.status='A'

but it's saying the syntax is wrong:

According to the manual, the SET clause expects a list of column assignments, meaning that the left side of each assignment must be a column reference, not a variable reference.

However, you can use variable assignment in the right hand side, where an expression goes, for instance like this:

IU.used_time = (@usedtime := unix_timestamp() - IU.start_date),

In the line above, the result of unix_timestamp() - IU.start_date is assigned to @usedtime, then the entire variable assignment expression evaluates to the same value, which is eventually stored into the IU.used_time column.

The variable assigned this way can then be used in other expressions that follow. Your update statement, therefore, could be rewritten e.g. like this:

UPDATE
    users IU
    INNER JOIN activity_stats DAS
        ON (IU.user_id=DAS.for_user_id)
SET 
    IU.used_time        = (@usedtime := unix_timestamp() - IU.start_date),
    IU.time_period_left = CASE WHEN (@timeleft := IU.total_subscription_time - @usedtime) > 0 THEN @timeleft ELSE 0 END,
    IU.status           = CASE WHEN @timeleft > 0 THEN 'A' ELSE 'I' END,
    DAS.total_time_used = DAS.total_time_used + @usedtime
WHERE
    IU.time_period_left > 0
    AND IU.status='A'
;

Note that, in addition to fixing the syntax in general, I have moved the IU.used_time column assignment to the top, so that the result of unix_timestamp() - IU.start_date could be re-used in the expression for @timeleft. You can keep your order of assignments, of course. Note, however, that IU.used_time in the last assignment

DAS.total_time_used = DAS.total_time_used + IU.used_time

may still hold the old value. So, even if you decide to keep the order of assignments, I would still recommend that you use an expression rather than a column reference there if you want to use an updated value in that assignment.

See a live demo of this syntax at dbfiddle.uk.

StackExchange Database Administrators Q#185004, answer score: 4