Conditional Insert and Update Using MERGE

View All Scripts Login to Run Script

Script Name Conditional Insert and Update Using MERGE
Description This example conditionally inserts and updates table data by using the MERGE statement.
Area SQL General
Referenced In Database SQL Language Reference
Contributor Oracle
Created Thursday January 28, 2016

Statement 1

A table named people_source is created.

CREATE TABLE people_source ( 
  person_id  INTEGER NOT NULL PRIMARY KEY, 
  first_name VARCHAR2(20) NOT NULL, 
  last_name  VARCHAR2(20) NOT NULL, 
  title      VARCHAR2(10) NOT NULL 
)

Table created.

Statement 2

A table named people_target is created.

CREATE TABLE people_target ( 
  person_id  INTEGER NOT NULL PRIMARY KEY, 
  first_name VARCHAR2(20) NOT NULL, 
  last_name  VARCHAR2(20) NOT NULL, 
  title      VARCHAR2(10) NOT NULL 
)

Table created.

Statement 3

The people_target table is populated.

INSERT INTO people_target VALUES (1, 'John', 'Smith', 'Mr')

1 row(s) inserted.

Statement 4

The people_target table is populated.

INSERT INTO people_target VALUES (2, 'alice', 'jones', 'Mrs')

1 row(s) inserted.

Statement 5

The people_source table is populated.

INSERT INTO people_source VALUES (2, 'Alice', 'Jones', 'Mrs.')

1 row(s) inserted.

Statement 6

The people_source table is populated.

INSERT INTO people_source VALUES (3, 'Jane', 'Doe', 'Miss')

1 row(s) inserted.

Statement 7

The people_source table is populated.

INSERT INTO people_source VALUES (4, 'Dave', 'Brown', 'Mr')

1 row(s) inserted.

Statement 8
```
COMMIT
```
```
Statement processed.
```

Statement 9

Display the contents of the people_source table.

SELECT * FROM people_source


PERSON_ID FIRST_NAME LAST_NAME TITLE
2 Alice Jones Mrs.
3 Jane Doe Miss
4 Dave Brown Mr

3 rows selected.

PERSON_ID	FIRST_NAME	LAST_NAME	TITLE
2	Alice	Jones	Mrs.
3	Jane	Doe	Miss
4	Dave	Brown	Mr

Statement 10

Display the contents of the people_target table.

SELECT * FROM people_target


PERSON_ID FIRST_NAME LAST_NAME TITLE
1 John Smith Mr
2 alice jones Mrs

2 rows selected.

Statement 11
This statement compares the contents of the people_target and people_source tables by using the person_id column and updates the rows in the people_target table only when there is a matching row in the people_source table. This statement is equivalent to: update people_target t set (t.first_name, t.last_name) = ( select s.first_name, s.last_name from people_source s where s.person_id = t.person_id ) where exists ( select null from people_source s where s.person_id = t.person_id).
```
MERGE INTO people_target pt 
USING people_source ps 
ON    (pt.person_id = ps.person_id) 
WHEN matched THEN UPDATE 
  SET pt.first_name = ps.first_name, 
      pt.last_name = ps.last_name, 
      pt.title = ps.title
```
```
1 row(s) updated.
```

PERSON_ID	FIRST_NAME	LAST_NAME	TITLE
1	John	Smith	Mr
2	alice	jones	Mrs

Statement 12

This statement displays the data in the people_target table after the MERGE operation is completed.

SELECT * FROM people_target


PERSON_ID FIRST_NAME LAST_NAME TITLE
1 John Smith Mr
2 Alice Jones Mrs.

2 rows selected.

Statement 13
Reverse the changes made to the people_target table.
```
ROLLBACK
```
```
Statement processed.
```
Statement 14
This statement compares the contents of the people_source and people_target tables by using the person_id column and inserts rows into the people_target table only when there is no matching row in the people_source table. This statement is equivalent to the following insert: insert into people_target ( person_id, first_name, last_name, title ) select s.person_id, s.first_name, s.last_name, s.title from people_source s where not exists ( select null from people_target t where s.person_id = t.person_id);
```
MERGE INTO people_target pt 
USING people_source ps 
ON    (pt.person_id = ps.person_id) 
WHEN NOT matched THEN INSERT 
  (pt.person_id, pt.first_name, pt.last_name, pt.title) 
  VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title )
```
```
2 row(s) inserted.
```

PERSON_ID	FIRST_NAME	LAST_NAME	TITLE
1	John	Smith	Mr
2	Alice	Jones	Mrs.

Statement 15

SELECT * FROM people_target


PERSON_ID FIRST_NAME LAST_NAME TITLE
1 John Smith Mr
2 alice jones Mrs
3 Jane Doe Miss
4 Dave Brown Mr

4 rows selected.

Statement 16
Reverse the changes made to the people_target table.
```
ROLLBACK
```
```
Statement processed.
```

PERSON_ID	FIRST_NAME	LAST_NAME	TITLE
1	John	Smith	Mr
2	alice	jones	Mrs
3	Jane	Doe	Miss
4	Dave	Brown	Mr

Statement 17

This statement compares the contents of the people_target and people_source tables by using the person_id column and conditionally inserts and updates data in the people_target table. For each matching row in the people_source table, the values in the people_target table are set to those from the people_source table. Any unmatched rows from the people_source table are added to the people_target table.

MERGE INTO people_target pt 
USING people_source ps 
ON    (pt.person_id = ps.person_id) 
WHEN matched THEN UPDATE 
  SET pt.first_name = ps.first_name, 
      pt.last_name = ps.last_name, 
      pt.title = ps.title 
WHEN NOT matched THEN INSERT 
  (pt.person_id, pt.first_name, pt.last_name, pt.title) 
  VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title )

3 row(s) updated.

Statement 18

SELECT * FROM people_target


PERSON_ID FIRST_NAME LAST_NAME TITLE
1 John Smith Mr
2 Alice Jones Mrs.
3 Jane Doe Miss
4 Dave Brown Mr

4 rows selected.

Statement 19
```
ROLLBACK
```
```
Statement processed.
```
Statement 20
This statement compares the people_source and people_target tables by using the person_id column. When the person_id matches, the corresponding rows in the people_target table are updated by using values from the people_source table. The DELETE clause removes all the rows in people_target where title = 'Mrs'. When the person_id does not match, the rows from the people_source table are added to the people_target table. The WHERE clause ensures that only rows that have title = 'Mr' are added to the to people_target table.
```
MERGE INTO people_target pt 
USING people_source ps 
ON    (pt.person_id = ps.person_id) 
WHEN matched THEN UPDATE 
  SET pt.first_name = ps.first_name, 
      pt.last_name = ps.last_name, 
      pt.title = ps.title 
  DELETE where pt.title  = 'Mrs.' 
WHEN NOT matched THEN INSERT 
  (pt.person_id, pt.first_name, pt.last_name, pt.title) 
  VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title) 
  WHERE ps.title = 'Mr'
```
```
2 row(s) updated.
```

PERSON_ID	FIRST_NAME	LAST_NAME	TITLE
1	John	Smith	Mr
2	Alice	Jones	Mrs.
3	Jane	Doe	Miss
4	Dave	Brown	Mr

Statement 21

SELECT * FROM people_target


PERSON_ID FIRST_NAME LAST_NAME TITLE
1 John Smith Mr
4 Dave Brown Mr

2 rows selected.

Statement 22
```
ROLLBACK
```
```
Statement processed.
```

PERSON_ID	FIRST_NAME	LAST_NAME	TITLE
1	John	Smith	Mr
4	Dave	Brown	Mr

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database