oracle 从临时表获取对表所做的所有更新

wwtsj6pe  于 2023-01-16  发布在  Oracle
关注(0)|答案(1)|浏览(162)

我有一个包含一些数据的表T1,它最初使用查询Q1更新它。现在,我每周使用相同的查询Q1在表T2中填充数据。T2就像一个保存最新数据的临时表。最终目标是让T1拥有T2的所有更新,其中FINDING_DATE根据T2的最新信息更新。

SQL> select * from T1;

ID    NAME                           FINDING_DATE
----- ------------------------------ --------------------
10    vivek                          29-12-2022
20    anand                          29-12-2022
30    vinod                          29-12-2022
40    nandu                          29-12-2022
50    sri                            29-12-2022

SQL> select * from T2;

ID    NAME                           FINDING_DATE
----- ------------------------------ --------------------
10    vivek                          06-01-2023
30    vinod                          06-01-2023
40    nandu                          06-01-2023
50    sri                            06-01-2023
60    nani                           06-01-2023

使用以下查询,我能够获得更新到T1的新数据:

insert into T1 select * from T2 where ID in 
(select ID from T2
minus
select ID from T1);

SQL> select * from T1;

ID    NAME                           FINDING_DATE
----- ------------------------------ --------------------
10    vivek                          29-12-2022
20    anand                          29-12-2022
30    vinod                          29-12-2022
40    nandu                          29-12-2022
50    sri                            29-12-2022
60    nani                           06-01-2023

这里只插入一个新记录,ID=20的记录不删除,FINDING_DATE也不更新。
从T2到T1获取所需数据更新的最佳方法是什么?

kmb7vmvb

kmb7vmvb1#

我是这么想的。
样本数据:

SQL> select * from t1;

        ID NAME  FINDING_DA
---------- ----- ----------
        10 vivek 29-12-2022
        20 anand 29-12-2022    --> doesn't have match in T2
        30 vinod 29-12-2022
        40 nandu 29-12-2022
        50 sri   29-12-2022

SQL> select * from t2;

        ID NAME  FINDING_DA
---------- ----- ----------
        10 vivek 06-01-2023
        30 vinod 06-01-2023
        40 nandu 06-01-2023
        50 sri   06-01-2023
        60 nani  06-01-2023
        99 LF    13-01-2023      --> additional row

Merge将更新现有行并插入缺少的行:

SQL> merge into t1
  2  using t2
  3  on (t1.id = t2.id)
  4  when matched then update set t1.finding_date = t2.finding_date
  5  when not matched then insert (id, name, finding_date)
  6    values (t2.id, t2.name, t2.finding_date);

6 rows merged.

结果:匹配行被更新,新行被添加,但ID = 20的非匹配行未被修改:

SQL> select * from t1 order by id;

        ID NAME  FINDING_DA
---------- ----- ----------
        10 vivek 06-01-2023
        20 anand 29-12-2022   --> it is still here
        30 vinod 06-01-2023
        40 nandu 06-01-2023
        50 sri   06-01-2023
        60 nani  06-01-2023
        99 LF    13-01-2023   --> newly added*emphasized text*

7 rows selected.

SQL>

如果要删除t2中不存在的行,必须使用两个语句:一个用于删除行,另一个用于执行更新/插入(或合并;这就是为什么它被称为 upsert)。
删除行可能很慢;截断表,因为看起来您并不真正关心旧值:

SQL> truncate table t1;

Table truncated.

SQL> merge into t1
  2  using t2
  3  on (t1.id = t2.id)
  4  when matched then update set t1.finding_date = t2.finding_date
  5  when not matched then insert (id, name, finding_date)
  6    values (t2.id, t2.name, t2.finding_date);

6 rows merged.

SQL> select * from t1 order by id;

        ID NAME  FINDING_DA
---------- ----- ----------
        10 vivek 06-01-2023    --> ID = 20 doesn't exist; of course not,
        30 vinod 06-01-2023    --> table was truncated
        40 nandu 06-01-2023
        50 sri   06-01-2023
        60 nani  06-01-2023
        99 LF    13-01-2023    --> newly added

6 rows selected.

SQL>

相关问题