Merge table into another table : Merge « Table « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Table
3. Merge

SQL> create table student_emails_ext
  2   (firstname    varchar(40),
  3    lastname     varchar(40),
  4    email        varchar(80) );

Table created.

SQL>
SQL> create table student_emails
  2  as select * from student_emails_ext
  3     where 0=1
  4  /

Table created.

SQL>
SQL>
SQL> merge into student_emails s
  2  using (select * from student_emails_ext) e
  3  on (s.firstname = e.firstname and s.lastname = e.lastname)
  4  when matched then
  5       update set s.email = e.email
  6  when not matched then
  7       insert (s.firstname, s.lastname, s.email)
  8       values(e.firstname , e.lastname, e.email);

0 rows merged.

SQL>
SQL> drop table student_emails_ext;

Table dropped.

SQL> drop table student_emails;

Table dropped.