The curious case of updating a value
The issue, described below is very strange when you find it, because it works only in MySql.
In MySql the update keeps track of the order of the parameters updated. So while for any known DBMS when you update a field computed considering another one (i.e. set A = 10, B = A…) the value of A and B are calculated starting from the original value for each update in MySql as soon as you update a field, this new value will be used instead of the original.
Besides this you can use the order clause to specify HOW update the values.
This cose works on MySql and on SQL SERVER:
create table testUpdate (id int primary key not null, valA decimal(10), valB decimal(10), valC decimal(10) ); insert into testUpdate values (1,0,0,0); insert into testUpdate values (2,0,0,0); insert into testUpdate values (3,0,0,0); -- update session update testUpdate set valA=100, valB=200, valC=valA+valB where id = 1; update testUpdate set valA=100,valC=valA+valB, valB=200 where id = 2; update testUpdate set valC=valA+valB, valA=100, valB=200 where id = 3; select * from testUpdate; drop table testUpdate;