Oracle的合并语句MERGE INTO 写法小例子

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;