mysql两表自动更新_mysql两张表批量更新

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;