dokuwiki

delete from emp_copy where employee_id in (SELECT EMP_COPY.EMPLOYEE_ID 
FROM EMP_COPY , DEPARTMENTS 
WHERE ( (EMP_COPY.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID) AND
(DEPARTMENTS.DEPARTMENT_NAME = 'IT') ))

select  *  from emp_copy where department_id = 60

select count(*) from emp_copy
-- select /*+ first_rows */ * from employees





merge into emp_copy c
using employees e
on (e.last_name = c.last_name and e.first_name = c.first_name)
/* when matched then
  update set salary = salary * 1.1 */
when not matched THEN
  insert (
   employee_id,
   first_name,
   last_name,
   email,
   hire_date,
   job_id,
   department_id,
   salary
  ) values (
   employees_seq.nextval,
   e.first_name,
   e.last_name,
   e.email,
   e.hire_date,
   e.job_id,
   e.department_id,
   e.salary
  )

merge into accountants a
using (
SELECT employees.employee_id, first_name, last_name, hire_date, salary, job_title
  FROM employees, jobs
  WHERE ((jobs.job_id = employees.job_id) 
  AND (jobs.job_title LIKE 'Account%')     )
) e
on (a.employee_id = e.employee_id)
when matched then update set a.salary = e.salary
when not matched then insert 
(first_name, last_name, employee_id, hire_date, salary, job_title)
values 
(e.first_name, e.last_name, e.employee_id, e.hire_date, e.salary, e.job_title)