1) 建表
DROP TABLE TEST1;
DROP TABLE TEST2;
CREATE TABLE TEST1(TESTID VARCHAR(5), TEXT VARCHAR(50), FLAG VARCHAR(10));
CREATE TABLE TEST2(TESTID VARCHAR(5), TEXT VARCHAR(50), FLAG VARCHAR(10));
2) 向表中插入数据
DELETE FROM TEST1;
DELETE FROM TEST2;
INSERT INTO TEST1 VALUES('1','AA', NULL);
INSERT INTO TEST1 VALUES('2','BB', NULL);
INSERT INTO TEST2 VALUES('1','AAAAAAA', NULL);
INSERT INTO TEST2 VALUES('2','BBBBBBB', NULL);
INSERT INTO TEST2 VALUES('3','CCCCCC', NULL);
INSERT INTO TEST2 VALUES('4','DDDDDD', NULL);
INSERT INTO TEST2 VALUES('5','EEEEEEE', NULL);
COMMIT;
3) 合并语句MERGE INTO 开始
MERGE INTO TEST1 c
USING TEST2 e
ON (c.TESTID = e.TESTID)
WHEN MATCHED THEN
UPDATE SET
c.TEXT = e.TEXT,
c.FLAG = 'UPDATE'
WHEN NOT MATCHED THEN
INSERT VALUES(e.TESTID, e.TEXT, 'INSERT');
COMMIT;