The use of merge in Oracle9i

Grammar:

MERGE [hint] into [schema.] table [T_alias] USING [schema.] {Table | view | subquery} [T_alias] On (condition) when matched THEN merge_update_clause if not matched THEN;

The merge was added to ORACLE 9I

Grammar:

MERGE [hint] into [schema.] table [T_alias] USING [schema.] {Table | view | subquery} [T_alias] On (condition) when matched THEN merge_update_clause if not matched THEN;

Building test data tables

CREATE TABLE Tj_test

(ID number,

Name VARCHAR2 (20),

Age number)

Inserting data into a table

INSERT INTO Tj_test

Values

(1, ' out of the ', 23)

INSERT INTO Tj_test

Values

(2, ' KK ', 22)

INSERT INTO Tj_test

Values

(3, ' Joe ', 27)

SELECT * FROM Tj_test

1, 23.

2 KK 22

3 Joe 27

Build another new table

CREATE TABLE Tj_test1 as SELECT * from Tj_test where 1=0

Insert a sum of data

INSERT INTO Tj_test1

Values

(1, ' jlk ', 23)

SELECT * FROM Tj_test1

1 JKL 23--note that the value in the Name field here is JKL

Use merge to implement update with no inserts

Merge into Tj_test1 tt1

Using Tj_test TT

On (tt1.id=tt.id)

When matched then

Update set

Tt1.name=tt.name,

Tt1.age=tt.age

When not matched then

Insert VALUES (

Tt.id,

Tt.name,

Tt.age)

Query Tj_test1 table (compare the data in the original table, update the field name in Id=1 row and two new data)

SELECT * FROM Tj_test1

1 out of 23--the original JKL value here is updated

3 Joe 27--Inserts not in the original table

2 KK 22--No inserts in the original table