I'm wanting to merge two tables together (tbl1 and tbl2)

Tbl1 is the master table containing all products and the price change history.

Tbl2 contains only products that have had changes made that day

I want to compare tbl2 against tbl1, and when the latest entry for a product on tbl1 has a different price than the price listed on tbl2, it should create a new line entry on tbl1. If the data matches, no changes are required.

I understand Merge into and Row_Number but that seems to work on an instance where I'm merging tbl1 into tbl2, not the other way

for example

MERGE INTO tbl1 USING tbl2
ON CONCAT(tbl1.product, tbl1.price) = CONCAT(tbl2.product, tbl2.price)
WHEN NOT MATCHED THEN
    INSERT (CHANGE_ID, product, price, UPDATED_AT, MODIFIED_DATETIME) 
            values (CONCAT(current_time, product), product, price, UPDATED_AT, MODIFIED_DATETIME);

This code will however search against all entries. What I was hoping to do was somehow use the ROW_NUMBER() like below, but it only seems possible to do ROW_NUMBER on the table your brining in to merge

MERGE INTO 
(select * FROM (SELECT ROW_NUMBER() OVER (PARTITION BY product ORDER BY MODIFIED_DATETIME DESC) AS ROWCOUNT, product, price FROM tbl1) where ROWCOUNT = 1) tblnew
USING tbl2
ON CONCAT(tblnew.product, tblnew.price) = CONCAT(tbl2.product, tbl2.price)
    WHEN NOT MATCHED THEN
        INSERT (CHANGE_ID, product, price, UPDATED_AT, MODIFIED_DATETIME) 
                values (CONCAT(current_time, product), product, price, UPDATED_AT, MODIFIED_DATETIME);

I have even tried creating a select clause first and setting that as the table then using that in the merge. I think this is more likely the correct way forward, I just can't seem to bring the table across for use in the merge

Select * from(select * FROM (SELECT ROW_NUMBER() OVER (PARTITION BY product ORDER BY MODIFIED_DATETIME DESC) AS ROWCOUNT, product, price FROM tbl1) where ROWCOUNT = 1) as tblnew
MERGE INTO tblnew USING tbl2
    ON CONCAT(tblnew.product, tblnew.price) = CONCAT(tbl2.product, tbl2.price)
        WHEN NOT MATCHED THEN
            INSERT (CHANGE_ID, product, price, UPDATED_AT, MODIFIED_DATETIME) 
                    values (CONCAT(current_time, product), product, price, UPDATED_AT, MODIFIED_DATETIME);

Would I be better using an INSERT INTO? Part of the reason I wanted to use MERGE INTO is to increment the modified_datetime for any that match, so I can identify products that have not been checked.

Edited to add source example

Tbl1

Tbl2

I would therefore expect apple to add a new row on with it's different than the last price held.

Tbl1 after merge

CodePudding user response：

Following query will give latest entry from tbl1 as per updated_dt

select * from tbl1 qualify row_number() 
over (partition by product order by updated_at desc)=1

Combine above query with data from tbl2 to get data as per condition " when the latest entry for a product on tbl1 has a different price than the price listed on tbl2" -

select distinct a.* from
tbl2 a,
(select * from tbl1 qualify row_number() 
over (partition by product order by updated_at desc)=1) b
where a.product = b.product
and a.price != b.price -- different price

To include additional rows in tbl2 that are missing from tbl1 UNION can be used as -

select distinct a.* from
tbl2 a,
(select * from tbl1 qualify row_number() 
over (partition by product order by updated_at desc)=1) b
where a.product = b.product
and a.price != b.price
UNION ALL -- Add any rows in tbl2 that are new and not present in tbl1
select * from Tbl2 where product not in (select product from tbl1);

Using above select INSERT can be performed as -

insert into tbl1 
select 
product||to_char(Updated_At,'dd/mm/yyyy'),product,price,updated_at,current_timestamp()
from
(
select distinct a.* from
tbl2 a,
(select * from tbl1 qualify row_number() 
over (partition by product order by updated_at desc)=1) b
where a.product = b.product
and a.price != b.price
UNION ALL
select * from Tbl2 where product not in (select product from tbl1)
);