Get all updates made to a table from a temporary table-CodePudding

i am having a table T1 with some data and it uses a query Q1 to update it initially.Now i am taking the same query Q1 and populating the data in a table T2 every week.T2 is like a temporary table to hold latest data. The end goal is for T1 to have all the updates from T2 where the FINDING_DATE is updated as per the latest info from 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

Using the below query i am able to get the new data updated to 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

Here only a new record is inserted and record with ID=20 is not deleted.Also the FINDING_DATE is not updated.

What is the best way to get the required data updates from T2 to T1.

CodePudding user response：

Here's what I think.

Sample data:

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 will update existing rows and insert missing ones:

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.

Result: matching rows are updated, new row is added, but non-matching row with ID = 20 wasn't modified in any way:

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>

If you want to get rid of rows that don't exist in t2, you'll have to use two statements: one to delete rows, and another to perform update/insert (or merge; that's why it is called upsert).

Deleting rows can be slow; truncate the table instead, as it seems that you don't really care about old values:

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>