Splitting MERGE statement in Update, Delete and Insert

841 Views Asked by At

I am trying to migrate one on SQL from Oracle to Hive. To make to work on hive , I have to break Merge statement into UPDATE/DELETE and INSERT.

Please help me on this. I have my version of splitting , but i am not sure on it.

Note: I am not using Hive MERGE for other reason.

Merge Statement:

MERGE INTO bonuses D
 USING (SELECT employee_id, salary, department_id FROM employees
 WHERE department_id = 80) S
 ON (D.employee_id = S.employee_id)
 WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01  WHERE (S.salary > 8000)
   DELETE WHERE (S.salary >= 10000)
 WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
   VALUES (S.employee_id, S.salary*.01)
   WHERE (S.salary <= 8000);

Table DDLs:

CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);
CREATE TABLE employees ( employee_id NUMBER, salary NUMBER, department_id int);
0

There are 0 best solutions below