dokuwiki

This is an old revision of the document!

---

merge into emp_copy c
using (select 1 from dual)
-- using (select 'bla' fn, 'bli' ln from dual) e
-- using ( select first_name, last_name from emp_copy )
on (:b_last_name = c.last_name and :b_first_name = c.first_name)
 when matched then
   update set salary        = :b_salary,             
              email         = :b_email,
              hire_date     = :b_hire_date,
              job_id        = :b_job_id ,
              department_id = :b_department_id
when not matched THEN
  insert (
   employee_id,
   first_name,
   last_name,
   email,
   hire_date,
   job_id,
   department_id,
   salary
  ) values (
   employees_seq.nextval,
   :b_first_name,
   :b_last_name,
   :b_email,
   :b_hire_date,
   :b_job_id,
   :b_department_id,
   :b_salary
  )

merge into accountants
using (select 1 from dual)
on (last_name = :ln and first_name = :fn)
when matched then update set salary = :salary
when not matched then 
  insert 
    (first_name,
    last_name,
    salary)
    values (
    :fn,
    :ln,
    :salary)