Summary of commonly used multi-table modification statements in Mysql and Oracle

//Create table create table aaa(id int,value1 int(5),value2 int(5),value3 int(5),value4 int(5));
create table bbb like aaa;
//Insert into aaa values(1,0,0,0,0),(2,0,0,0,0),(3,0,0,0,0),(4,0,0,0,0),(5,0,0,0,0),(6,0,0,0,0),(7,0,0,0,0);
insert into bbb values(1,11,12,13,14),(3,31,32,33,34),(5,51,52,53,54);
//Backup create table a1 select * from aaa;
create table b1 select * from bbb;

//General multi-table modification update a1 set a1.value1 = (select b1.value1 from b1 where a1.id = b1.id),
a1.value2 = (select b1.value2 from b1 where a1.id = b1.id);

//Table association multi-table modification update a1 inner join b1 on a1.id = b1.id set a1.value1 = b1.value1,
a1.value2 = b1.value2,a1.value3 = b1.value3;

--Create table create table aaa(id number,value1 number,value2 number,value3 number,value4 number);
create table bbb(id number,value1 number,value2 number,value3 number,value4 number);
--Insert datainsert into aaa values(1,0,0,0,0);insert into aaa values(2,0,0,0,0);insert into aaa values(3,0,0,0,0);
insert into aaa values(4,0,0,0,0);insert into aaa values(5,0,0,0,0);insert into aaa values(6,0,0,0,0);
insert into aaa values(7,0,0,0,0);insert into bbb values(1,11,12,13,14);
insert into bbb values(3,31,32,33,34);insert into bbb values(5,51,52,53,54);
-- Backup create table a1 as select * from aaa;
create table b1 as select * from bbb;

//General multi-table modification update a1 set a1.value1 = (select b1.value1 from b1 where a1.id = b1.id),
a1.value2 = (select b1.value2 from b1 where a1.id = b1.id);

merge into target table a ---The target table pointed to by into is the table we want to update.
using source table b ---The table pointed to by using cannot be changed. It can also be a view, result set, etc.
on(a.id = b.id) --- on() contains the conditions for matching the target table and the source table. If the conditions are met, the match is successful.
when matched then ---when matched then is followed by a modification statement.
	update set ... 
when not matched then ---when not matched then is followed by a new statement.
	insert values(....);

merge into a1 using b1 
on (a1.id = b1.id) 
when matched then update 
	set a1.value1 = b1.value1,a1.value2 = b1.value2,a1.value3 = b1.value3;

update a1 set a1.value1 = 99,a1.value2 = 999,a1.value3 = 999 
where exists(select value1 from b1 where a1.id = b1.id);