create external table nonacid_n1 (key int, a1 string, value string) stored as orc;
insert into nonacid_n1 values(1, 'a11', 'val1');
insert into nonacid_n1 values(2, 'a12', 'val2');
+-----------------+----------------+-------------------+
| nonacid_n1.key | nonacid_n1.a1 | nonacid_n1.value |
+-----------------+----------------+-------------------+
| 1 | a11 | val1 |
| 2 | a12 | val2 |
+-----------------+----------------+-------------------+
create table acidTable(key int NOT NULL enable, a1 string DEFAULT 'a1', value string) stored as orc tblproperties ("transactional"="true");
insert into acidTable values(1, 'a10','val100');
+----------------+---------------+------------------+
| acidtable.key | acidtable.a1 | acidtable.value |
+----------------+---------------+------------------+
| 1 | a10 | val100 |
+----------------+---------------+------------------+
MERGE INTO acidTable as t using nonacid_n1 as s ON t.key = s.key
WHEN MATCHED AND s.key < 3 THEN DELETE
WHEN MATCHED AND s.key > 3 THEN UPDATE set a1 = "DEFAULT"
WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.a1, "DEFAULT");
+----------------+---------------+------------------+
| acidtable.key | acidtable.a1 | acidtable.value |
+----------------+---------------+------------------+
| 2 | a12 | DEFAULT |
+----------------+---------------+------------------+