Differences

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

--- kurs:merge_werte_einfuegen [2010/05/18 11:15]
127.0.0.1 external edit
+++ kurs:merge_werte_einfuegen [2014/10/14 15:38] (current)
mh
@@ Line 1: / Line 1: @@
+<code>
+merge into emp_merge m
+using
+(select  1 employee_id, -- sequence not allowed here
+        'Hofstetter' last_name,
+        'IT_PROG' job_id,
+salary,
+        sysdate hire_date
+        from dual) e
+on (m.last_name = e.last_name)
+when matched then
+  update set salary = e.salary
+when not matched then
+insert (
+  employee_id,
+  last_name,
+  job_id,
+  salary,
+  hire_date
+  ) values (
+  seq.nextval,
+  e.last_name,
+  e.job_id,
+  e.salary,
+  e.hire_date
+  )
+</code>
+<code>
+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
+  )
+</code>
 <code>
 merge into accountants

dokuwiki

User Tools

Site Tools

Differences

Page Tools