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?
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)