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