mysql两张表批量更新

为什么80%的码农都做不了架构师？>>>   

create table testa (
	STU_ID varchar (60),
	STU_NAME varchar (60),
	GRADE double 
); 
create table testb (
	STU_ID varchar (60),
	STU_NAME varchar (60),
	GRADE double 
); 

insert into testa (STU_ID, STU_NAME, GRADE) values('1','张三','100');
insert into testa (STU_ID, STU_NAME, GRADE) values('2','李四','69');
insert into testa (STU_ID, STU_NAME, GRADE) values('3','小明','70');
insert into testa (STU_ID, STU_NAME, GRADE) values('4','','15');

insert into testb (STU_ID, STU_NAME, GRADE) values('1','张三','20');
insert into testb (STU_ID, STU_NAME, GRADE) values('3','小明','10');
insert into testb (STU_ID, STU_NAME, GRADE) values('4','','15');

要求：根据stu_id相等，把testb表的grade更新到testa表中！

下面这种写法会存在问题。

执行下面这条sql以后，你会发现testa里面stu_id=2 的grade变为了null

UPDATE testa a SET a.grade = (SELECT b.grade FROM testb b WHERE b.STU_ID=a.STU_ID);

mysql 貌似是不支持update from写法

下面的这种写法就可以避免上面grade变为null的情况

update testa a inner join testb b on a.stu_id=b.stu_id set a.grade=b.grade
where a.stu_id=b.stu_id;

另外附上Oracle下循环更新的方法：

begin for p in (select * from testb)loop
update testa a set a.grade=p.grade where a.stu_id=p.stu_id;
end loop;
end;

转载于:https://my.oschina.net/u/865478/blog/121704