Oracle两表关联更新

目录

表结构、测试数据

需求：

方式1：update

方式2：内联视图更新

方式3：merge

---

表结构、测试数据

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');

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 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);

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;

UPDATE (select t1.code code1, t2.code code2
          from t1 t1, t2 t2
         where t1.name = t2.name) t
   set code1 = code2;

方式3：merge