mysql - How to share a common subexpression in an UPDATE? -

consider (contrived) query:

update table1   left join table2 on ...   set     table1.a = table1.x + table2.y,     table1.b = table1.a * 2; 

it updates table1 based on correlated data table2. note table1.b supposed updated using (just computed) value of table1.a.

according mysql docs,

single-table update assignments evaluated left right. multiple-table updates, there no guarantee assignments carried out in particular order.

in tests, columns not updated in order need, perhaps because mysql thinks multiple-table update (although 1 table gets updated).

i tried using user-defined variable store result this:

update table1   left join table2 on ...   set     table1.a = @sum,     table1.b = @sum * 2       @sum := table1.x + table2.y; 

that didn't work either (the fields weren't updated null values). , again, docs isn't supported.

i aware can create stored function compute subexpression, several reasons doesn't seem worth effort.

so, there other way share subexpression between assignments? goal of sharing both prevent recomputation , avoid duplication in source code, although more of latter former.

the simplest way can think of join subquery, presuming table1 has primary key constraint on id -

update table1 join (   select table1.id, table1.x + table2.y xy   table1   left join table2 on ... ) base   on (base.id = table1.id) set table1.a = base.xy,     table1.b = base.xy * 2; 

although, if bothers you, can exploit variable assignment -

update table1 left join table2 on ... set table1.a = (@sum := table1.x + table2.y),     table1.b = @sum * 2;