如果有只存在于T_A表中的数据,上面语句会把这些数据的类型更改成null,如果需要不更改 update T_A set 类型=nvl(( select 类型 from T_B where T_A.班级 = T_B.班级 AND T_A.姓名 = T_B.姓名),类型);
谢谢楼上的,我执行后报错ORA-01427:单行子查询返回多个行
T_B中有重复纪录。用下面的语句查下 select 班级,姓名,count(*) from T_B group by 班级,姓名 having count(*) > 1;
--也可以简单的忽略,取个最大值, update T_A set 类型=( select max(类型) from T_B where T_A.班级 = T_B.班级 AND T_A.姓名 = T_B.姓名);
借用下奔驰同学的指令(方法3要有PK或者唯一索引): --1 merge into A using B on(A.name=B.name) when matched then update set A.price = B.price;--2 update A set A.price = (select B.price from A,B where A.name = B.name) where exists(select 1 from A,B where A.name = B.name);--3 update( select A.price p1,B.price p2 from A,B where A.name = B.name) set p1=p2;
update T_A set 类型=nvl((
select 类型 from T_B where T_A.班级 = T_B.班级 AND T_A.姓名 = T_B.姓名),类型);
select 班级,姓名,count(*) from T_B group by 班级,姓名 having count(*) > 1;
update T_A set 类型=(
select max(类型) from T_B where T_A.班级 = T_B.班级 AND T_A.姓名 = T_B.姓名);
--1
merge into A
using B
on(A.name=B.name)
when matched then
update set A.price = B.price;--2
update A
set A.price = (select B.price from A,B where A.name = B.name)
where exists(select 1 from A,B where A.name = B.name);--3
update(
select A.price p1,B.price p2
from A,B
where A.name = B.name)
set p1=p2;