Is it possible to update a variable after using it (rather than before)?

Question

@x=0
select
  case
    when col>1 then @x:=@x+1
    when col=1 then @x
    else @x; @x:=@x+1
  end as x
from t

I want to do something like what's seen above in MySQL. The contents between the WHEN and THEN are arbitrary. However, what I want to do is seen after the ELSE statement. I know I can set a user variable where I can either output its value in the SELECT (by just using @x), or output some updated value (by using @x:=@x+1). What I want to do though is output the value @x, while also updating it @x:=@x+1. Is this possible?

Answer 1

CASE statement and CASE operator don't behave the same way. In a SELECT, it's the CASE operator. Each when/then and the else can only contain one expression returning 1 result. On the other hand, the CASE statement behaves like in other languages, where each conditions can have a block of code of any length.

Also, even if this syntax was possible, there is a big risk to do something like this. There is no guarantee at all in which order records will be processed, so the result could be totally different from one execution to another.

You may think adding a ORDER BY will solve this problem. But, since ORDER BY is the last section of the SELECT statement to be executed, the calculation would be done before the rows are sorted anyway.

Answer 2

The case expression needs to return a single value. To get another @x include it as a separate SELECT result like:

select
  @x,
  case
    when seq>2 then @x:=@x+1
    when seq=1 then @x
    else @x:=@x+1
  end as x
from seq_0_to_10

ref: fiddle