Oracle merge into 学习

用途

将A表的数据输出到B表中，根据条件判断B表中是否有数据，有则更新，没有则插入

语法

merge into table1 alias1
    using table2 alias2
    on (condition)
when matched then
    update set col1=col2
when not matched then
    insert (col1) values (col2)

示例

MERGE INTO SCOTT.TEST_USERS m0 USING (
    SELECT
        t0.USERID,
        t0.USERNAME
    FROM
        SCOTT.USERS
) m1 ON (m0.USERID = m1.USERID)
WHEN MATCHED THEN
    UPDATE
SET USERNAME = m1.USERNAME
WHEN NOT MATCHED THEN
    INSERT (USERID, USERNAME)
VALUES
    (m1.USERID, m1.USERNAME)