需求,
1.删除A表中存在,B表中不存在的数据
2.添加A表中不存在,B表中存在的数据
3.修改A表与B表ID相等的记录,设置A.COL_VALUE = B.COL_VALUE;
CREATE TABLE EMPA ( ID INT PRIMARY KEY NOT NULL, EMP_NAME VARCHAR(20) ); CREATE TABLE EMPB ( ID INT PRIMARY KEY NOT NULL, EMP_NAME VARCHAR(20) ) INSERT INTO EMPA VALUES(1,'A'); INSERT INTO EMPA VALUES(2,'B'); INSERT INTO EMPA VALUES(3,'C'); INSERT INTO EMPA VALUES(4,'E'); INSERT INTO EMPA VALUES(5,'F'); INSERT INTO EMPB VALUES(3,'E'); INSERT INTO EMPB VALUES(4,'F'); INSERT INTO EMPB VALUES(5,'G'); SELECT * FROM EMPA; SELECT * FROM EMPB; MERGE EMPA AS TARGET USING (SELECT * FROM EMPB) AS SOURCE ON (TARGET.ID = SOURCE.ID) WHEN MATCHED THEN UPDATE SET TARGET.EMP_NAME = SOURCE.EMP_NAME WHEN NOT MATCHED BY TARGET THEN INSERT (ID,EMP_NAME) VALUES(SOURCE.ID,SOURCE.EMP_NAME) WHEN NOT MATCHED BY SOURCE THEN DELETE ; SELECT * FROM EMPA; SELECT * FROM EMPB;