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;


I commenti sono chiusi.