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;