User Tools

Site Tools


kurs:merge_accountants

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision
Previous revision
kurs:merge_accountants [2009/09/24 13:14]
mh created
kurs:merge_accountants [2010/10/30 14:56]
mh
Line 1: Line 1:
 +<​code>​
 +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
 +  )
 +</​code>​
 +
 +<​code>​
 +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)
 +</​code>​
kurs/merge_accountants.txt ยท Last modified: 2014/09/10 21:22 (external edit)