ORACLE两表关联更新三种方式

浅浅的花香味﹌ 2023-10-07 18:12 35阅读 0赞

不多说了，我们来做实验吧。

## 准备工作 ##

创建如下表数据

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

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZUtpbmc_size_10_color_FFFFFF_t_70_g_se_x_16][]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZUtpbmc_size_10_color_FFFFFF_t_70_g_se_x_16 1][]

现需求：参照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);

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZUtpbmc_size_17_color_FFFFFF_t_70_g_se_x_16][]

如果同时更新多个字段可以参照以下语法：

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;

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZUtpbmc_size_20_color_FFFFFF_t_70_g_se_x_16][]

## 方式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;

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZUtpbmc_size_13_color_FFFFFF_t_70_g_se_x_16][]

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZUtpbmc_size_10_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/742d53308f4f40cf8f10c871beaf7250.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAVGVyZW5jZUtpbmc=,size_10,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZUtpbmc_size_10_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/8a9ed54a8bdd40d7a0f2f5e0ec8e80a0.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAVGVyZW5jZUtpbmc=,size_10,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZUtpbmc_size_17_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/673276f7ac2a4b968e4d7cba377695bf.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAVGVyZW5jZUtpbmc=,size_17,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZUtpbmc_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/37c761ee267e4cdfa8d746b7ace4b4fa.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAVGVyZW5jZUtpbmc=,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZUtpbmc_size_13_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/7eb0467efa2f4dd2a186b2669c2cb983.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAVGVyZW5jZUtpbmc=,size_13,color_FFFFFF,t_70,g_se,x_16