Unexpected result from query based on old column value I need to update a column and based on the old value, update a second column.

After googling I came up with THIS (*nr 4) solution: copy the table to have the old value at hand.

The table looks like

   id | upd | a1 | a2 .. | b1 | b2 | b3 |
   1  |     | 1  | 1     | 1  | 1  | 1  |


My query looks like:

UPDATE table t1, table t2 SET
    t1.b1  = GREATEST( 5, t1.a1),
    t1.upd = IF(t1.b1 &lt;&gt; t2.b1 ,1 , t1.upd)
WHERE t1.id = t2.id


Based on this query I would expect the table to look like this:

   id | upd | a1 | a2 .. | b1 | b2 | b3 |
   1  | 1*  | 1  | 1     | 5* | 1  | 1  |
*= updated


Only, it isn't. It executes the first line correctly (updating b1 to 5), but not the second one, setting the upd field.

The mysql manual states:

The second assignment in the following statement sets col2 to the current (updated) col1 value,
not the original col1 value. The result is that col1 and col2 have the same value.
 This behavior differs from standard SQL. 
UPDATE t1 SET col1 = col1 + 1, col2 = col1;


So I would have expected the second line (update upd) to work as well. 

If I add (as a test) these two lines

t1.b2=t1.b1,
t1.b3=t2.b1,


before the t1.upd = line, b2 and b3 still show 1, where I expected b2 to be 5.

Where am I going wrong in my logic (and understanding of MySQL :-)?

NOTE Sorry, I forget to mention: I connot use Triggers. Otherwis it would have been less difficult
I would try this one:

UPDATE 
    table t1 
SET
    upd = 1, 
    b1 = GREATEST(5, a1) 
WHERE
    b1 &lt;&gt; GREATEST(5, a1) ;


No need for "copying" the table or using the (not 100% sure) assumption that MySQL updates the columns in a specific order.

The answers in your link are about a much more difficult to do (in MySQL) operation, i.e. swapping the values of 2 columns. 



If you want to update multiple columns and set the upd=1 column if at least one of the other columns is updated, you can use a simple OR in the WHERE clause:

UPDATE 
    table t1 
SET
    upd = 1, 
    b1 = GREATEST(5, a1),
    b2 = GREATEST(7, a2),
    b3 = &lt;whatever&gt; 
    --- 
WHERE
   ( b1 &lt;&gt; GREATEST(5, a1)
  OR b2 &lt;&gt; GREATEST(7, a2)
  OR b3 &lt;&gt; &lt;whatever&gt; 
  ---
   ) ;