Hot File

MERGE Statement Enhancements in Oracle Database 10g

View: 485    Dowload: 0   Comment: 0   Post by: naruto  
Author: none   Category: Oracle   Fields: Education - Schools

0 point/2 review File has been tested

Oracle 10g includes a number of amendments to the MERGE statement making it more flexible.

Introduction

Related articles.

Test Table

The following examples use the table defined below.

CREATE TABLE test1 AS
SELECT *
FROM   all_objects
WHERE  1=2;

Optional Clauses

The MATCHED and NOT MATCHED clauses are now optional making all of the following examples valid.

-- Both clauses present.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status);

-- No matched clause, insert only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status);

-- No not-matched clause, update only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status;

Conditional Operations

Conditional inserts and updates are now possible by using a WHERE clause on these statements.

-- Both clauses present.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID'
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status)
    WHERE  b.status != 'VALID';

-- No matched clause, insert only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status)
    WHERE  b.status != 'VALID';

-- No not-matched clause, update only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID';

DELETE Clause

An optional DELETE WHERE clause can be added to the MATCHED clause to clean up after a merge operation. Only those rows in the destination table that match both the ON clause and the DELETE WHERE are deleted. Depending on which table the DELETE WHERE references, it can target the rows prior or post update. The following examples clarify this.

Create a source table with 5 rows as follows.

CREATE TABLE source AS
SELECT level AS id,
       CASE
         WHEN MOD(level, 2) = 0 THEN 10
         ELSE 20
       END AS status,
       'Description of level ' || level AS description
FROM   dual
CONNECT BY level <= 5;

SELECT * FROM source;

        ID     STATUS DESCRIPTION
---------- ---------- -----------------------
         1         20 Description of level 1
         2         10 Description of level 2
         3         20 Description of level 3
         4         10 Description of level 4
         5         20 Description of level 5

5 rows selected.

SQL>

Create the destination table using a similar query, but this time with 10 rows.

CREATE TABLE destination AS
SELECT level AS id,
       CASE
         WHEN MOD(level, 2) = 0 THEN 10
         ELSE 20
       END AS status,
       'Description of level ' || level AS description
FROM   dual
CONNECT BY level <= 10;

SELECT * FROM destination;

         1         20 Description of level 1
         2         10 Description of level 2
         3         20 Description of level 3
         4         10 Description of level 4
         5         20 Description of level 5
         6         10 Description of level 6
         7         20 Description of level 7
         8         10 Description of level 8
         9         20 Description of level 9
        10         10 Description of level 10

10 rows selected.

SQL>

The following MERGE statement will update all the rows in the destination table that have a matching row in the source table. The additional DELETE WHERE clause will delete only those rows that were matched, already in the destination table, and meet the criteria of the DELETE WHERE clause.

MERGE INTO destination d
  USING source s
    ON (s.id = d.id)
  WHEN MATCHED THEN
    UPDATE SET d.description = 'Updated'
    DELETE WHERE d.status = 10;

5 rows merged.

SQL>

SELECT * FROM destination;

        ID     STATUS DESCRIPTION
---------- ---------- -----------------------
         1         20 Updated
         3         20 Updated
         5         20 Updated
         6         10 Description of level 6
         7         20 Description of level 7
         8         10 Description of level 8
         9         20 Description of level 9
        10         10 Description of level 10

8 rows selected.

SQL>

Notice there are rows with a status of "10" that were not deleted. This is because there was no match between the source and destination for these rows, so the delete was not applicable.

The following example shows the DELETE WHERE can be made to match against values of the rows before the update operation, not after. In this case, all matching rows have their status changed to "10", but the DELETE WHERE references the source data, so the status is checked against the source, not the updated values.

ROLLBACK;

MERGE INTO destination d
  USING source s
    ON (s.id = d.id)
  WHEN MATCHED THEN
    UPDATE SET  d.description = 'Updated',
                d.status = 10
    DELETE WHERE s.status = 10;

5 rows merged.

SQL>
  
SELECT * FROM destination;

        ID     STATUS DESCRIPTION
---------- ---------- -----------------------
         1         10 Updated
         3         10 Updated
         5         10 Updated
         6         10 Description of level 6
         7         20 Description of level 7
         8         10 Description of level 8
         9         20 Description of level 9
        10         10 Description of level 10

8 rows selected.

SQL>

Notice, no extra rows were deleted compared to the previous example.

By switching the DELETE WHERE to reference the destination table, the extra updated rows can be deleted also.

ROLLBACK;

MERGE INTO destination d
  USING source s
    ON (s.id = d.id)
  WHEN MATCHED THEN
    UPDATE SET  d.description = 'Updated',
                d.status = 10
    DELETE WHERE d.status = 10;

5 rows merged.

SQL>
  
SELECT * FROM destination;

        ID     STATUS DESCRIPTION
---------- ---------- -----------------------
         6         10 Description of level 6
         7         20 Description of level 7
         8         10 Description of level 8
         9         20 Description of level 9
        10         10 Description of level 10

5 rows selected.

SQL>

 

MERGE Statement Enhancements in Oracle Database 10g

MERGE Statement Enhancements in Oracle Database 10g Posted on 20-03-2014  Oracle 10g includes a number of amendments to the MERGE statement making it more flexible. 5/10 485

Comment:

To comment you must be logged in members.

Files with category

  • The basic functions in Oracle ( P3 )

    View: 583    Download: 0   Comment: 0   Author: none  

    The basic functions in Oracle ( P3 )

    Category: Oracle
    Fields: Other

    0/2 review
    The basic functions in Oracle ( P3 ) Blog / MSSQL database - oracle - This article is a sequel of the song " The basic functions in Oracle ( P2 ) " . In this section alone will solve all the functions and character strings in group 1 . Are you ready...

  • The basic functions in Oracle (P2)

    View: 634    Download: 0   Comment: 0   Author: none  

    The basic functions in Oracle (P2)

    Category: Oracle
    Fields: Other

    0/4 review
    The basic functions in Oracle ( P2 ) Blog / MSSQL database - oracle - Today 2Cwebvn please write to the jaw in a series on Oracle , the first post we have a vision of all the basic functions in Oracle , today I will go all details about the group is...

  • The basic functions in Oracle (P1)

    View: 507    Download: 0   Comment: 0   Author: none  

    The basic functions in Oracle (P1)

    Category: Oracle
    Fields: Other

    0/2 review
    The basic functions in Oracle ( P1 ) Blog / MSSQL database - oracle - Today 2Cwebvn would like to introduce you to the basic functions of Oracle , said that the basic function but here is a combination of functions in Oracle . This article is quite...

  • Oracle 10g Grid Control Installation On Red Hat Enterprise Linux 3 (RHEL3)

    View: 509    Download: 0   Comment: 0   Author: none  

    Oracle 10g Grid Control Installation On Red Hat Enterprise Linux 3 (RHEL3)

    Category: Oracle
    Fields: Education - Schools

    0/2 review
    In this article I'll describe the installation of Oracle 10g Grid Control on Red Hat Enterprise Linux 3 (RHEL3). The article is based on a server installation with a minimum of 2G swap and the following package groups installed.

  • Oracle Database 10g Release 2 (10.2.0.1) Installation On Fedora Core 4 (FC4)

    View: 509    Download: 0   Comment: 0   Author: none  

    Oracle Database 10g Release 2 (10.2.0.1) Installation On Fedora Core 4 (FC4)

    Category: Oracle
    Fields: Education - Schools

    0/2 review
    In this article I'll describe the installation of Oracle Database 10g Release 2 (10.2.0.1) on Fedora Core 4. The article is based on a Fedora Core 4 Server Installation with a minimum of 2G swap, secure Linux disabled and the following package groups...

  • MERGE Statement Enhancements in Oracle Database 10g

    View: 485    Download: 0   Comment: 0   Author: none  

    MERGE Statement Enhancements in Oracle Database 10g

    Category: Oracle
    Fields: Education - Schools

    0/2 review
    Oracle 10g includes a number of amendments to the MERGE statement making it more flexible.

  • Installation Enhancements in Oracle Database 10g

    View: 560    Download: 0   Comment: 0   Author: none  

    Installation Enhancements in Oracle Database 10g

    Category: Oracle
    Fields: Education - Schools

    0/2 review
    Installation of the Oracle 10g database software has been enhanced in several ways. This articles provides a brief summary of the improvements

  • File Upload and Download Procedures in Oracle Database 10g

    View: 737    Download: 0   Comment: 0   Author: none  

    File Upload and Download Procedures in Oracle Database 10g

    Category: Oracle
    Fields: Education - Schools

    0/3 review
    This article is an update of a previous Oracle9i article, which includes some modified configuration steps necessary in Oracle 10g. For this procedure to work the HTTP Server software, available on the Companion CD, must be installed into a separate...

 
Newsletter Email

File suggestion for you

File top downloads

logo codetitle
Codetitle.com - library source code to share, download the file to the community
Copyright © 2015. All rights reserved. codetitle.com Develope by Vinagon .Ltd