1. 測試一
create table test(id int, tag int, num int);
insert into test (id, tag, num) values(1, 1, 1), (2,2, 2), (3,3,3);
update test
set tag = 4, num=case when tag=4 then 4 else 3 end
where tag=3;
select * from test;
(1)sqlserver2014的結果:
(2)MySQL的結果:
2. 測試二:更換set語句的順序
create table test(id int, tag int, num int);
insert into test (id, tag, num) values(1, 1, 1), (2,2, 2), (3,3,3);
update test
set num=case when tag=4 then 4 else 3 end, tag = 4<喎?http://www.Bkjia.com/kf/ware/vc/" target="_blank" class="keylink">vc3Ryb25nPgp3aGVyZSB0YWc9MzsKPGJyPgpzZWxlY3QgKiBmcm9tIHRlc3Q7Cjxicj4KCjxzdHJvbmc+o6gxo6lzcWxzZXJ2ZXIyMDE0tcS94bn7o7o8L3N0cm9uZz4KPGltZyBzcmM9"http://www.2cto.com/uploadfile/Collfiles/20140327/2014032709015710.jpg" alt="\">
(2)MySQL的結果
結論:
(1)MySQL的update語句,set列的順序是有關系的,後面列的計算是以前面列的結果為基礎的,即從左向右評估;
(2)SQLServer的update語句,set的順序無關,所有的更改都是基於之前取出的快照;