CREATE TABLE mt1 (a int not null primary key, b int not null, key (b));
CREATE TABLE mt2 (a int not null primary key, b int not null, key (b));
INSERT INTO mt1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
INSERT INTO mt2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
update mt1,mt2 set mt1.a=mt1.a+100;
--sorted_result
select * from mt1;
update mt1,mt2 set mt1.a=mt1.a+100 where mt1.a=101;
--sorted_result
select * from mt1;
update mt1,mt2 set mt1.b=mt1.b+10 where mt1.b=2;
--sorted_result
select * from mt1;
update mt1,mt2 set mt1.b=mt1.b+2,mt2.b=mt1.b+10 where mt1.b between 3 and 5 and mt2.a=mt1.a-100;
--sorted_result
select * from mt1;
--sorted_result
select * from mt2;
update mt1,mt2 set mt1.b=mt2.b, mt1.a=mt2.a where mt1.a=mt2.a and not exists (select * from mt2 where mt2.a > 10);
drop table mt1,mt2;