今天在sql训练题库中看到了这题,这是一道很有代表性的多表修改题,其实解出这道题并不难,无论是mysql中还是oracle中都有很多种解法,接下来就好好归纳一下这些解法。
msyql中多表修改
对于mysql中常用的多表修改语句,还是用例子来解释一下吧。
//建表
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);
//备份
create table a1 select * from aaa;
create table b1 select * from bbb;
一般多表修改:(在mysql和Oracle中都适用,下面也会解释Oracle中的)
//一般多表修改
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);