This content has been marked as final. Show 3 replies
This method works but is there an easier (faster) way to update another table with new data only?
Almost anything would be better than that slow-by-slow loop processing.
You don't need a procedure you should just use MERGE for that. See the examples in the MERGE section of the SQL Language doc
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 DELETE WHERE (S.salary > 8000) WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus) VALUES (S.employee_id, S.salary*.01) WHERE (S.salary <= 8000);
1 person found this helpful
is there an easier (faster) way to update another table with new data only?Your requirement exactly meets the purpose of the MERGE statement.
When there is a need, where you need to process a set of data and based on some condition it needs to be INSERTED or UPDATED.
So, instead of performing this in a LOOP i.e. row-by-row (slow-by-slow) manner, you can do this using a single SQL statement.
And always SQL is faster than Pl/Sql (as there's no context switching between SQL Engine and Pl/Sql Engine).
Fantastic - have never used merge before, this is exactly was i was looking for.
Thank you !!