나의 향기에 젖다...

[예제] JOIN UPDATE

UPDATE TABLE_MAIN
      SET TABLE_MAIN.REGUSR = '쫑'
   FROM TABLE_JOIN
 WHERE TABLE_JOIN.SEQNUM = TABLE_MAIN.SEQNUM
     AND TABLE_JOIN.PROGBN = 'Y'

[예제] JOIN DELETE

DELETE TABLE_MAIN
   FROM TABLE_JOIN
 WHERE TABLE_JOIN.SEQNUM = TABLE_MAIN.SEQNUM
     AND TABLE_JOIN.PROGBN = 'Y'

조인을 이용한 UPDATE/DELETE 구문의 FROM 절 이후는 똑같다.

## 1 ##

DELETE 
  FROM 테이블1 A
 WHERE EXISTS
            (

 SELECT 1
   FROM 테이블2 B
 WHERE A.테이블1_키 = B.테이블2_키
);

DELETE
  FROM T_CLASS_DIAG A
 WHERE EXISTS

(
 SELECT 1
   FROM T_PORTFOLIO_HISTORY
  WHERE LECTURE_ID = A.LECTURE_ID
      AND USER_ID = A.USER_ID

);

## 2 ##

DELETE

  FROM SG2EXAMT T1
 WHERE (T1.PATNO,T1.ORDDATE,T1.RESVTYPE)
          = (SELECT PATNO,ORDDATE,RESVTYPE
                FROM SG1RESVT T2
               WHERE T2.CUSTCODE =  '125000'
                   AND T2.ACPTDATE = TO_DATE('20100329','YYYYMMDD')
             );