有如下两张表a表
主键是patient_id,visit_id
patient_id, visit_id, start_time
001 1 2010-01-01
002 1 2010-01-02
..............b表
主键是patient_id,visit_id,z1,z2
patient_id visit_id, zd_sj, z1, z2
001 1 null 1 3
001 1 null 2 3
002 1 null 1 4
............我想把b表中的zd_sj更新为a表中的start_time
条件是:
a.patient_id=b.patient_id and a.visit_id=b.visit_id
and b.z1='1' and b.z2='3'
请问在oracle中如何写update?
谢谢
主键是patient_id,visit_id
patient_id, visit_id, start_time
001 1 2010-01-01
002 1 2010-01-02
..............b表
主键是patient_id,visit_id,z1,z2
patient_id visit_id, zd_sj, z1, z2
001 1 null 1 3
001 1 null 2 3
002 1 null 1 4
............我想把b表中的zd_sj更新为a表中的start_time
条件是:
a.patient_id=b.patient_id and a.visit_id=b.visit_id
and b.z1='1' and b.z2='3'
请问在oracle中如何写update?
谢谢
and b.z1='1' and b.z2='3') where exists(select 1 from a where a.patient_id=b.patient_id and a.visit_id=b.visit_id
and b.z1='1' and b.z2='3')
update b set zd_sj=(select start_time from a
where a.patient_id=b.patient_id and a.visit_id=b.visit_id
and b.z1='1' and b.z2='3')
where exists(select 1 from a where a.patient_id=b.patient_id and a.visit_id=b.visit_id
and b.z1='1' and b.z2='3')
merge into ta a using tb b
on (a.patient_id=b.patient_id and a.visit_id=b.visit_id
and b.z1='1' and b.z2='3')
when matched then update set a.zd_sj=b.start_time
//*****************************
不懂这句是什么意思
exists(select 1 from a where a.patient_id=b.patient_id and a.visit_id=b.visit_id
and b.z1='1' and b.z2='3')
//*********************************
1.为什么要用exists能否详解?
2.在这句中 select 后面的1又代表什么?
3.如果我改为这样行吗!如下:
update b set zd_sj=(select start_time from a
where a.patient_id=b.patient_id and a.visit_id=b.visit_id)
where b.z1='1' and b.z2='3'谢谢各位高手!请帮解释一下!
2.在这句中 select 后面的1又代表什么? -- 因为exists返回的true或者false,结果没有意义,所以用1代替
3.如果我改为这样行吗!如下:
update b set zd_sj=(select start_time from a
where a.patient_id=b.patient_id and a.visit_id=b.visit_id)
where b.z1='1' and b.z2='3'
--你的这个语句有缺陷,更新整个表,将不满足条件的记录也更新掉了
顺序没有反啊:我想把b表中的zd_sj更新为a表中的start_time
update a
set a.a1=(select语句)
where exists(select语句)
就是说where 后面只用exitst(select语句)其余的任何条件都不需要;
并且两个select语句必须一致。
谢谢!
update b set zd_sj=(select start_time from a
where a.patient_id=b.patient_id and a.visit_id=b.visit_id b.z1='1' and b.z2='3')
语句2:
update b set zd_sj=(select start_time from a
where a.patient_id=b.patient_id and a.visit_id=b.visit_id
and b.z1='1' and b.z2='3')
where exists(select 1 from a where a.patient_id=b.patient_id and a.visit_id=b.visit_id
and b.z1='1' and b.z2='3')
这两句有什么区别?执行效果是不是一样的?
update b set zd_sj=(select start_time from a
where a.patient_id=b.patient_id and a.visit_id=b.visit_id b.z1='1' and b.z2='3')
语句2:
update b set zd_sj=(select start_time from a
where a.patient_id=b.patient_id and a.visit_id=b.visit_id
and b.z1='1' and b.z2='3')
where exists(select 1 from a where a.patient_id=b.patient_id and a.visit_id=b.visit_id
and b.z1='1' and b.z2='3')
这两句有什么区别?执行效果是不是一样的?有区别,作用域不一样啊语句一:更新b表,所有的zd_sj字段,更新全表数据
语句二:更新b表中,满足where exists(select 1 from a where a.patient_id=b.patient_id and a.visit_id=b.visit_id
and b.z1='1' and b.z2='3')条件的zd_sj字段,更新部分记录
create table x(pi number,vi number,st char(20));
alter table x add constraint x_p primary key(pi,vi);insert into x values('001','1','2010-09-08');
create table y(pi number,vi number,zs char(20),z1 number,z2 number);
alter table y add constraint y_p primary key(pi,vi,z1,z2);insert into y values('002','1',null,1,3);insert into y values('001','1',null,1,3);
//#########
merge into y using x
on(x.pi=y.pi and x.vi=y.vi
and y.z1='1' and y.z2='3')
when matched then update set y.zs=x.st
//#########
update y set zs=(select st from x
where x.pi=y.pi and x.vi=y.vi
and y.z1='1' and y.z2='3')