merge语法

MERGE INTO target_table --目标表（需要执行插入、修改、删除操作的表）USING source_table      --使用USING子句指定要更新或插入的数据源。源可以是表、视图或子查询的结果。ON search_condition     --匹配条件    WHEN MATCHED THEN   --操作语句        UPDATE SET col1 = value1, col2 = value2,...        WHERE <update_condition>        [DELETE WHERE <delete_condition>]    WHEN NOT MATCHED THEN        INSERT (col1,col2,...)        values(value1,value2,...)        WHERE <insert_condition>;

CREATE TABLE members (    member_id NUMBER PRIMARY KEY,    first_name VARCHAR2(50) NOT NULL,    last_name VARCHAR2(50) NOT NULL,    rank VARCHAR2(20));--快速新建另外一个表CREATE TABLE member_staging AS SELECT * FROM members;

--插入数据-- insert into members table    INSERT INTO members(member_id, first_name, last_name, rank) VALUES(1,'Abel','Wolf','Gold');INSERT INTO members(member_id, first_name, last_name, rank) VALUES(2,'Clarita','Franco','Platinum');INSERT INTO members(member_id, first_name, last_name, rank) VALUES(3,'Darryl','Giles','Silver');INSERT INTO members(member_id, first_name, last_name, rank) VALUES(4,'Dorthea','Suarez','Silver');INSERT INTO members(member_id, first_name, last_name, rank) VALUES(5,'Katrina','Wheeler','Silver');INSERT INTO members(member_id, first_name, last_name, rank) VALUES(6,'Lilian','Garza','Silver');INSERT INTO members(member_id, first_name, last_name, rank) VALUES(7,'Ossie','Summers','Gold');INSERT INTO members(member_id, first_name, last_name, rank) VALUES(8,'Paige','Mcfarland','Platinum');INSERT INTO members(member_id, first_name, last_name, rank) VALUES(9,'Ronna','Britt','Platinum');INSERT INTO members(member_id, first_name, last_name, rank) VALUES(10,'Tressie','Short','Bronze');
-- insert into member_staging tableINSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(1,'Abel','Wolf','Silver');INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(2,'Clarita','Franco','Platinum');INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(3,'Darryl','Giles','Bronze');INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(4,'Dorthea','Gate','Gold');INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(5,'Katrina','Wheeler','Silver');INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(6,'Lilian','Stark','Silver');

MERGE INTO member_staging x   --目标表（需要执行插入、修改、删除操作的表）USING (SELECT member_id, first_name, last_name, rank FROM members) y  --数据源表ON (x.member_id  = y.member_id)  --匹配条件--匹配到的数据，按照数据源表的数据进行更新WHEN MATCHED THEN              UPDATE SET x.first_name = y.first_name,                         x.last_name = y.last_name,                         x.rank = y.rank    WHERE x.first_name <> y.first_name OR            x.last_name <> y.last_name OR            x.rank <> y.rank 
--目标表中匹配不到数据源表的数据，在这里将匹配不到的数据插入目标表（指数据源中有的，而目标表中没有的数据，可以执行insert操作）    WHEN NOT MATCHED THEN                INSERT(x.member_id, x.first_name, x.last_name, x.rank)           VALUES(y.member_id, y.first_name, y.last_name, y.rank)

SELECT * FROM members ;SELECT * FROM member_staging order by member_id asc;