How to update data of table

MIX 2000 0
Viewed 18

Hi I have two tables and I want to update the data of table 2 from table 1 based on the date .

Table 1:

Id Date Value
1 8-june-2022 2
1 9-june-2022 5

Table 2:

Id Date Value
1 2-june-2022
1 6-june-2022

Table 2 after update:

Id Date Value
1 2-june-2022 2
1 6-june-2022 5

The common column is the id and I want to update the data based on id and date.In other words the data should be taken from table 1 in order and update table 2 data also by order

sql-update oracle
1 Answers

If the values of the id - "Date" column pairs are unique for throughout the both of the tables, then use a MERGE statement along with an analytic function such as DENSE_RANK() as in the following case

MERGE INTO table2 t2 
USING (SELECT t1.id AS id1, t1."Date" AS date1, t1.value AS value1,
              t2.id AS id2, t2."Date" AS date2, t2.value AS value2, 
              DENSE_RANK() OVER (PARTITION BY t1.id ORDER BY t1."Date") AS rn1,
              DENSE_RANK() OVER (PARTITION BY t2.id ORDER BY t2."Date") AS rn2              
         FROM table1 t1
         JOIN table2 t2 
           ON t1.id = t2.id) tt
          ON ( t2.id = tt.id1 AND tt.date2 = t2."Date" AND rn1 = rn2 )
 WHEN MATCHED THEN UPDATE SET t2.value = tt.value1

where I've just replaced the Date with "Date". Since DaTe(case insensitive) is a reserved keyword and cannot be used as a column name unless double-quoted.

Demo

Barbaros Özhan0
Related