准备工作
创建如下表数据
drop table demo_t1; drop table demo_t2; CREATE TABLE DEMO_T1 ( FNAME VARCHAR2(20) , FMONEY VARCHAR2(20) ); ALTER TABLE demo_t1 ADD PRIMARY KEY(FNAME); insert into demo_t1 (fname,fmoney) values('A','20'); insert into demo_t1 (fname,fmoney) values('B','30'); CREATE TABLE DEMO_T2 ( FNAME VARCHAR2(20) , FMONEY VARCHAR2(20) ); ALTER TABLE demo_t2 ADD PRIMARY KEY(FNAME); insert into demo_t2 (fname,fmoney) values('C','10'); insert into demo_t2 (fname,fmoney) values('D','20'); insert into demo_t2 (fname,fmoney) values('A','100');
现需求：参照T2表，修改T1表，修改条件为两表的fname列内容一致。
方式1：update
UPDATE DEMO_T1 t1 SET T1.FMONEY = (select T2.FMONEY from DEMO_T2 T2 where T2.FNAME = T1.FNAME) WHERE EXISTS(SELECT 1 FROM DEMO_T2 T2 WHERE T2.FNAME = T1.FNAME);
如果同时更新多个字段可以参照以下语法：
UPDATE DEMO_T1 t1 SET (字段一,字段二,...) = (select 字段一,字段二,... from DEMO_T2 T2 where T2.FNAME = T1.FNAME) WHERE EXISTS(SELECT 1 FROM DEMO_T2 T2 WHERE T2.FNAME = T1.FNAME);
方式2：内联视图更新
注意：需要取数据的表，该字段必是主键或者有唯一约束
UPDATE ( select t1.fmoney fmoney1,t2.fmoney fmoney2 from demo_t1 t1,demo_t2 t2 where t1.fname = t2.fname )t set fmoney1 =fmoney2;
方式3：merge更新
merge into demo_t1 t1 using (select t2.fname,t2.fmoney from demo_t2 t2) t on (t.fname = t1.fname) when matched then update set t1.fmoney = t.fmoney;
