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)