Search code examples
mergehanaupsert

Merge two SELECTs in HANA?


I have a table T1 as shown below with no primary keys

Account_ID Order_Number Article_Number Price
1 100 01 100,95
1 100 02 59,89
2 500 01 80
2 600 01 40

I have a view V1 where I want to update the data of the original table T1 with. It has the same structure also with no primary keys

Account_ID Order_Number Article_Number Price
1 100 01 200
1 100 02 79
3 800 01 5000

So the end result of T1 should be as follows after the update:

Account_ID Order_Number Article_Number Price
1 100 01 200
1 100 02 79
2 500 01 80
2 600 01 40
3 800 01 5000

Any ideas how can this be done with a query?


Solution

  • MERGE INTO T1 USING SELECT Account_ID,Order_Number, Article_Number, Price
    FROM T2
    WHEN MATCHED THEN
    UPDATE SET t1.Account_ID=t2.Account_ID, t1.Order_Number=t2.Order_Number, t1.Article_Number=t2.Article_Number, t1.Price=t2.Price
    WHEN NOT MATCHED THEN
    INSERT INTO T1 (Account_ID,Order_Number, Article_Number, Price) VALUES(t2.Account_ID,t2.Order_Number, t2.Article_Number, t2.Price)