MERGE 연습

WHEN MATCHED 사용
* member 의 자료

SQL> select * from member;

USERID     USERNAME   PASSWD     REGDATE      COUNTRY
---------- ---------- ---------- ------------ ----------
modify     test       1234       11-JAN-12    Korea
test       test       1234       11-JAN-12    Korea
gyh214     young      1234       10-JAN-12    Korea
gy14       hoon       1234       10-JAN-12    Korea 

*member2의 자료

SQL> select * from member2;

USERID     USERNAME   PASSWD     REGDATE      COUNTRY
---------- ---------- ---------- ------------ ----------
test       MERGE      1234       11-JAN-12    Korea
gyh214     MERGE      1234       10-JAN-12    Korea
gy14       MERGE      1234       10-JAN-12    Korea
 

** MERGE 실행

  1  merge into member
  2  using member2
  3  on(member.userid = member2.userid)
  4  when matched then
  5     update set
  6*    member.username = member2.username
SQL> /

3 rows merged.

Elapsed: 00:00:00.00
--------------------------------------------
--------------------------------------------
실행 후 member 의 자료
--------------------------------------------
--------------------------------------------
SQL> select * from member;

USERID     USERNAME   PASSWD     REGDATE      COUNTRY
---------- ---------- ---------- ------------ ----------
modify     test       1234       11-JAN-12    Korea
test       MERGE      1234       11-JAN-12    Korea
gyh214     MERGE      1234       10-JAN-12    Korea
gy14       MERGE      1234       10-JAN-12    Korea 

----------------------------------
WHEN NOT MATCHED 사용
* member 의 자료

 SQL> select * from member;

USERID     USERNAME   PASSWD     REGDATE      COUNTRY
---------- ---------- ---------- ------------ ----------
modify     test       1234       11-JAN-12    Korea
test       MERGE      1234       11-JAN-12    Korea
gyh214     MERGE      1234       10-JAN-12    Korea
gy14       MERGE      1234       10-JAN-12    Korea

*member2 의 자료

SQL> select * from member2;

USERID     USERNAME   PASSWD     REGDATE      COUNTRY
---------- ---------- ---------- ------------ ----------
test       MERGE      1234       11-JAN-12    Korea
CHANGED    MERGE      1234       10-JAN-12    Korea
gy14       MERGE      1234       10-JAN-12    Korea 

*MERGE 실행

SQL> merge into member
  2  using member2
  3  on(member.userid = member2.userid)
  4  when not matched then
  5     insert(userid, username, passwd, regdate,country)
  6     values(member2.userid, member2.username, member2.passwd
  7             , member2.regdate, member2.country);

1 row merged.

Elapsed: 00:00:00.01
--------------------------------------------
--------------------------------------------
실행 후 member 의 자료
--------------------------------------------
--------------------------------------------
SQL> select * from member;

USERID     USERNAME   PASSWD     REGDATE      COUNTRY
---------- ---------- ---------- ------------ ----------
modify     test       1234       11-JAN-12    Korea
test       MERGE      1234       11-JAN-12    Korea
CHANGED    MERGE      1234       10-JAN-12    Korea  <<행 추가 확인
gyh214     MERGE      1234       10-JAN-12    Korea
gy14       MERGE      1234       10-JAN-12    Korea
 

USERID가 'CHANGED'인 행이 member에는 없으므로 WHEN NOT MATCHED 조건에 걸려서 행을 insert하게 됨.
(*MERGE에서는 이미 into할 테이블이 MERGE INTO에서 정해졌으므로, insert문에 into를 쓸 필요가 없습니다.)