Systems Engineering and RDBMS

MERGE command enhancements in Oracle 10g

Posted by decipherinfosys on November 14, 2007

We had covered the MERGE command in Oracle before in our blog post. This is also a feature that will be present in SQL Server 2008. You can search this site togo over the other new feature sets that are being introduced in SQL Server 2008 (search for SQL Server 2008 on this blog site for getting to those posts). In this post today, we will cover the enhancements that were made to the MERGE command in Oracle 10g.

Oracle introduced MERGE command in version 9i. It is also known as UPSERT (Insert and Update statement together). Using the MERGE command, we can write insert and update statement using single SQL statement rather than coding two separate sets of SQLs, one for insert and one for update. It contains two clauses, WHEN MATCHED THEN (used for updating existing records) and WHEN NOT MATCHED THEN (used for inserting new records). In 9i, when we use MERGE command, we have to use both these clauses. In release 10g, Oracle made some enhancements to MERGE command. In 10g, both these clauses are optional and we can use any one of them to insert new records or to update the existing records. Let us see that with example. We will first create empty table and will populate it with some data. Connect to schema using proper credentials and execute following SQL.

CREATE TABLE TEST
(
OBJECT_ID NUMBER NOT NULL,
OBJECT_NAME VARCHAR2(128),
STATUS VARCHAR2(7),
CREATE_DATE DATE,
CONSTRAINT PK_TEST PRIMARY KEY(OBJECT_ID)
)
/

INSERT INTO TEST(OBJECT_ID, OBJECT_NAME, STATUS)
SELECT OBJECT_ID, OBJECT_NAME, STATUS
FROM USER_OBJECTS
WHERE STATUS = ‘INVALID’
/
COMMIT;

In our schema, we have two records in the invalid status, so table will be populated with two records. It will be different for your test case. Following is the result set.

OBJECT_ID OBJECT_NAME STATUS CREATE_DATE

———- ——————– ——- ———–

65879 TEST_PROC INVALID

65880 TIB_NEW INVALID

Now we will use traditional MERGE statement and will examine the results.

MERGE INTO test t
USING (SELECT object_id, object_name, status, created FROM user_objects) u
ON (t.object_id = u.object_id)
WHEN MATCHED THEN
UPDATE SET t.CREATE_DATE = u.created
WHEN NOT MATCHED THEN
INSERT (t.object_id, t.object_name, t.status, t.create_date)
VALUES (u.object_id, u.object_name, u.status, u.created)
/

In the above statement,
• MERGE INTO is used to merge (insert / update) records into the target table. In our case it is test table.
• USING clause is used to specify query on the source table from which we want to pull the data. Instead of query, we can define source table it self, if we are interested in all the columns.
• ON condition clause, identifies the match criteria.
• WHEN MATHCED THEN clause, is for updating matching records based on the ‘ON’ clause.
• WHEN NOT MATCHED THEN clause is to insert missing records from the source table.

Here is the data selected from the table after MERGE statement.

SQL>SELECT object_id, object_name, status, create_date from TEST;

OBJECT_ID OBJECT_NAME STATUS CREATE_DATE

———- ——————– ——- ————

65879 TEST_PROC INVALID 06-AUG-07

65880 TIB_NEW INVALID 06-AUG-07

65878 PK_NEW VALID 06-AUG-07

65877 NEW_TEST VALID 06-AUG-07

73391 PK_TEST VALID 12-NOV-07

73390 TEST VALID 12-NOV-07

Now let us delete the records with the ‘VALID’ status.

SQL> DELETE FROM TEST WHERE STATUS = ‘VALID’;

Once records are deleted, let us use WHEN MATCHED and WHEN NOT MATCHED clause separately. This is one of the major enhancements for the MERGE statement in 10g release, which adds more flexibility to the statement.

SQL> MERGE INTO test t
USING (SELECT object_id FROM user_objects) u
ON (t.object_id = u.object_id)
WHEN MATCHED THEN
UPDATE SET t.CREATE_DATE = sysdate;

Above statement only updates the matching records from the source table. Output is shown below.

OBJECT_ID OBJECT_NAME STATUS CREATE_DATE

———- ——————– ——- ————

65879 TEST_PROC INVALID 12-NOV-07

65880 TIB_NEW INVALID 12-NOV-07

Now let us insert records separately. We can also use WHERE clause to filter the records, we are interested in.

SQL> MERGE INTO test t
USING (SELECT object_id, object_name, status FROM user_objects) u
ON (t.object_id = u.object_id)
WHEN NOT MATCHED THEN
INSERT (t.object_id, t.object_name, t.status, t.create_date)
VALUES (u.object_id, u.object_name, u.status, sysdate)
WHERE u.status = ‘VALID’;

Here we are inserting records with only valid status which does not exist in the target table. Complete output of the table is as shown below.

SQL> SELECT object_id, object_name, status, create_date from TEST;

OBJECT_ID OBJECT_NAME STATUS CREATE_DATE

———- ——————– ——- ——————–

65879 TEST_PROC INVALID 12-NOV-07

65880 TIB_NEW INVALID 12-NOV-07

65878 PK_NEW VALID 12-NOV-07

65877 NEW_TEST VALID 12-NOV-07

73391 PK_TEST VALID 12-NOV-07

73390 TEST VALID 12-NOV-07

We can also delete the records using MERGE statement. Only those records from the destination table will be deleted which are updated by MERGE operation. That means DELETE WHERE clause evaluates the updated records not the original records.

SQL> MERGE INTO test t
USING (SELECT object_id, object_name, status FROM user_objects) u
ON (t.object_id = u.object_id)
WHEN MATCHED THEN
UPDATE SET t.create_date = SYSDATE – 1
DELETE WHERE (t.status = ‘INVALID’);

Above query will delete the records with invalid status because all the records in the table are updated irrespective of status. Here is the result set.

OBJECT_ID OBJECT_NAME STATUS CREATE_DATE

———- ——————– ——- ————

65878 PK_NEW VALID 11-NOV-07

65877 NEW_TEST VALID 11-NOV-07

73391 PK_TEST VALID 11-NOV-07

73390 TEST VALID 11-NOV-07

Let us rollback the change and re-issue the statement after making change such that not all the records are updated.

SQL> MERGE INTO test t
USING (SELECT object_id, object_name, status FROM user_objects) u
ON (t.object_id = u.object_id)
WHEN MATCHED THEN
UPDATE SET t.create_date = SYSDATE – 1
WHERE t.STATUS = ‘VALID’
DELETE WHERE (t.status = ‘INVALID’);

Above statement updates only those records which are in ‘valid’ status based on the matching object_id so delete statement will not delete any records because records in ‘invalid’ status are not updated by MERGE statement. Result set will be as under after executing the above statement.

OBJECT_ID OBJECT_NAME STATUS CREATE_DATE

———- ——————– ——- ——————–

65879 TEST_PROC INVALID 12-NOV-07

65880 TIB_NEW INVALID 12-NOV-07

65878 PK_NEW VALID 12-NOV-07

65877 NEW_TEST VALID 12-NOV-07

73391 PK_TEST VALID 12-NOV-07

73390 TEST VALID 12-NOV-07

As explained, MERGE statement is very convenient to combine the multiple DML statements. One thing to keep in mind though is, for the update clause, we cannot update the columns which are references in ON condition clause.

One Response to “MERGE command enhancements in Oracle 10g”

  1. […] It has existed since quite some time. We have also blogged about it on our site – including the enhancements to that command that were made in Oracle 10g. It gets introduced in SQL Server 2008 as well which is a welcome addition for all the […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: