Oracle merge into 学习

用途

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

语法

1
2
3
4
5
6
7
merge into table1 alias1
using table2 alias2
on (condition)
when matched then
update set col1=col2
when not matched then
insert (col1) values (col2)

示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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)
查看评论