INSERT INTO tablenm(

column1,

column2,

column3,

column4

)VALUES(

'value1',

'value2',

'value3',

now()

)

ON DUPLICATE KEY UPDATE

column2 = 'value4',

column3 = 'value5', 

column4 = now()

MERGE INTO TB_SCORE S

    USING TMP_SCORE T

       ON (S.COURSE_ID = T.COURSE_ID AND S.STUDENT_ID = T.STUDENT_ID)

    WHEN MATCHED THEN

        UPDATE SET S.SCORE = T.SCORE

    WHEN NOT MATCHED THEN

        INSERT (S.COURSE_ID, S.STUDENT_ID, S.SCORE) 

        VALUES (T.COURSE_ID, T.STUDENT_ID, T.SCORE)

with upsert as 

(

update student set 

age = #{age}

where

id = #{id}

returning *

)

insert into student

( id, name, age 0

select #{id}, #{name}, #{age}

where not exists(select * from upsert);

UPDATE emp e

 INNER JOIN dept d

    ON d.deptno = e.deptno

   SET e.dname = d.dname

;

-- 1. SubQuery 를 이용한 Update

UPDATE emp e

   SET e.dname = (SELECT d.dname FROM dept d WHERE d.deptno = e.deptno)

 WHERE EXISTS (SELECT 0 FROM dept d WHERE d.deptno = e.deptno)

;

-- 2. Updatable Join View 이용

--    단, d.deptno 가 반드시 PK 이어야 함

--    10G 까지는 PK 아니더라도 힌트로 제어 가능(/*+ bypass_ujvc */)

UPDATE /*+ bypass_ujvc */

       (SELECT e.dname

             , d.dname AS dname_new

          FROM emp  e

             , dept d

         WHERE d.deptno = e.deptno

        )

   SET dname = dname_new

;

-- 3. Merge

MERGE INTO emp e

USING dept d

ON (d.deptno = e.deptno)

WHEN MATCHED THEN

UPDATE SET e.dname = d.dname

;

출처 : http://www.gurubee.net/article/79308