Oracle两表关联更新
表结构、测试数据
drop table t1;
drop table t2;
CREATE TABLE T1
(
name VARCHAR2(10)
, code VARCHAR2(10)
);
ALTER TABLE t1 ADD PRIMARY KEY(name);
insert into t1 (name,code) values('lw01','01');
insert into t1 (name,code) values('lw02','02');
CREATE TABLE T2
(
name VARCHAR2(10)
, code VARCHAR2(10)
);
ALTER TABLE t2 ADD PRIMARY KEY(name);
insert into t2 (name,code) values('lw03','03');
insert into t2 (name,code) values('lw02','lw02');
insert into t2 (name,code) values('lw01','lw01');
需求:
参照T2表,修改T1表,修改条件为两表的name列内容一致。
方式1:update
UPDATE T1 t1
SET t1.code =
(select t2.code from T2 t2 where t2.name = t1.name)
WHERE EXISTS (SELECT 1 FROM T2 t2 WHERE t2.name = t1.name);
如果同时更新多个字段可以参照以下语法:
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.code code1, t2.code code2
from t1 t1, t2 t2
where t1.name = t2.name) t
set code1 = code2;
方式3:merge