title: How to use user-defined variables in an UPDATE statement?
body: <p>I was trying to answer another <a href="https://stackoverflow.com/questions/18404726">SO question</a> and was suddenly faced with the following problem. Points should be assigned to the 3 highest scoring (<code>mrk</code>) groups (<code>grp</code>) of each class (<code>sec</code>). The highest scoring groups get 5 points, the second ranking ones 3 points and the groups in 3rd rank only 1 point. For all others <code>pts</code> should be set to <code>null</code>.</p>
  
  <pre><code>| ID | SEC | GRP | MRK |    PTS |
  |----|-----|-----|-----|--------|
  |  1 | cl2 |  ge |  32 | (null) |
  |  2 | cl1 |  gb |  22 | (null) |
  |  3 | cl1 |  gd |  22 | (null) |
  |  4 | cl1 |  ge |  18 | (null) |
  |  5 | cl2 |  ga |  26 | (null) |
  |  6 | cl1 |  ga |  55 | (null) |
  |  7 | cl2 |  gb |  66 | (null) |
  |  8 | cl2 |  gc |  15 | (null) |
  |  9 | cl1 |  gc |  12 | (null) |
  | 10 | cl2 |  gf |   5 | (null) |
  | 11 | cl2 |  ge |  66 | (null) |
  </code></pre>
  
  <p>I chose to work with user-defined variables as they provide maximum flexibility regarding the allocation scheme and soon came up with the following solution:</p>
  
  <pre><code>SELECT id,sec,grp,mrk,
  CASE WHEN @s=sec THEN          -- whenever there is a new class ...
   CASE WHEN @m=mrk THEN @i ELSE -- issue the same points for 
                                 -- identical scorers, otherwise ...
    CASE WHEN IF(@m:=mrk,@i,@i)&gt;2 THEN @i:=@i-2  -- store mrk in @mrk and 
                                 -- while @i&gt;2 return points: 3 or 1 ...
                                  ELSE @i:=null  -- no points for the rest
    END
   END
   ELSE NULLIF(@i:=5,(@s:=sec)=(@m:=mrk)) -- store sec in @s and mrk in @m
                                          -- and return points: 5
  END pts
  FROM tbl ORDER BY sec,mrk desc
  </code></pre>
  
  <p>Explanation of <code>NULLIF(@i:=5,(@s:=sec)=(@m:=mrk))</code>:</p>
  
  <p>The expressions <code>@s:=sec</code> and <code>@m:=mrk</code> are both evaluated and then their values are compared by <code>=</code>. The result can either be <code>0</code> (false) or <code>1</code> (true) but it will definitely be unequal to <code>5</code>, the other argument of the <code>NULLIF</code> function, therefore in the end only the first argument (<code>5</code>) will be returned. I chose the construct to make the two variable assignments happen without returning anything.</p>
  
  <p>OK, maybe not the most straightforward solution ;-), but I <em>did pay attention</em> to define each variable only once for each record that is being processed, since "the order of evaluation for expressions involving user variables is undefined" <a href="http://dev.mysql.com/doc/refman/5.0/en/user-variables.html" rel="nofollow noreferrer">mysql manual</a>. The <code>select</code> indeed gives me the desired </p>
  
  <p>result:</p>
  
  <pre><code>| ID | SEC | GRP | MRK |    PTS |
  |----|-----|-----|-----|--------|
  |  6 | cl1 |  ga |  55 |      5 |
  |  2 | cl1 |  gb |  22 |      3 |
  |  3 | cl1 |  gd |  22 |      3 |
  |  4 | cl1 |  ge |  18 |      1 |
  |  9 | cl1 |  gc |  12 | (null) |
  |  7 | cl2 |  gb |  66 |      5 |
  | 11 | cl2 |  ge |  66 |      5 |
  |  1 | cl2 |  ge |  32 |      3 |
  |  5 | cl2 |  ga |  26 |      1 |
  |  8 | cl2 |  gc |  15 | (null) |
  | 10 | cl2 |  gf |   5 | (null) |
  </code></pre>
  
  <p>Now, my question is:</p>
  
  <p><strong>How do I write an <code>UPDATE</code> statement along the same lines that will store the above calculated results in column <code>pts</code>?</strong></p>
  
  <p>My attempts so far have all failed:</p>
  
  <pre><code>UPDATE tbl SET pts=
  CASE WHEN @s=sec THEN
   CASE WHEN @m=mrk THEN @i ELSE
    CASE WHEN IF(@m:=mrk,@i,@i)&gt;2 THEN @i:=@i-2 
                                  ELSE @i:=null 
    END
   END
   ELSE NULLIF(@i:=5,(@s:=sec)=(@m:=mrk)) 
  END
  ORDER BY sec,mrk desc
  </code></pre>
  
  <p>result:</p>
  
  <pre><code>| ID | SEC | GRP | MRK | PTS |
  |----|-----|-----|-----|-----|
  |  6 | cl1 |  ga |  55 |   5 |
  |  2 | cl1 |  gb |  22 |   5 |
  |  3 | cl1 |  gd |  22 |   5 |
  |  4 | cl1 |  ge |  18 |   5 |
  |  9 | cl1 |  gc |  12 |   5 |
  |  7 | cl2 |  gb |  66 |   5 |
  | 11 | cl2 |  ge |  66 |   5 |
  |  1 | cl2 |  ge |  32 |   5 |
  |  5 | cl2 |  ga |  26 |   5 |
  |  8 | cl2 |  gc |  15 |   5 |
  | 10 | cl2 |  gf |   5 |   5 |
  </code></pre>
  
  <p>Why does the update statement only get a single value (5) for pts?!?</p>
  
  <p>You can find all the data and SQL statements in my <a href="http://sqlfiddle.com/#!2/b1461/6" rel="nofollow noreferrer">SQLfiddle</a>.</p>