Merging Rows

Merging Rows

In Oracle The MERGE statement inserts or updates rows in one table by using data from another table. Each row is inserted or updated in the target table depending on an equijoin condition.

This example matches the employee_id in the tab_new table to the employee_id in the employees table. If a match is found, the row is updated to match the row in the employees table.
If the row is not found, it is  inserted into the tab_new table.

The complete example for using MERGE in a PL/SQL block is shown on the next notes page.


Now let us create new table tab_new.

SQL> create table tab_new as select employee_id eid,first_name fname,last_name lname from employees;

Table created.

Describe the structure of tab_new table.

SQL> desc tab_new;

 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 EID                                                NUMBER(6)
 FNAME                                              VARCHAR2(20)
 LNAME                                     NOT NULL VARCHAR2(25)

Delete all records from tab_new table.

SQL> truncate table tab_new;

Table truncated.
--------
After deleting records from tab_new , now you can insert  new records using in tabl_new table with the follwing command.

SQL> insert into tab_new values(101,'shweta','Deshpande');

1 row created.

SQL> insert into tab_new values(102,'shalina','khan');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into tab_new values(103,'rafat','shaikh');

1 row created.

SQL> commit;

Commit complete.

Now view all the records from tab_new table.

SQL> select * from tab_new;

       EID FNAME                LNAME
---------- -------------------- -------------------------
       101 shweta                Deshpande
       102 shalina               khan
       103 rafat                shaikh


Now write an Annonymous block to inserts or updates rows in one table by using  merge statement.

SQL>   merge into tab_new t
  2    using employees e
  3    on (t.eid=e.employee_id)
  4    when matched then
  5    update set
  6    t.fname=e.first_name,
  7    t.lname=e.last_name
  8   when not matched then
  9  insert values (e.employee_id,e.first_name,e.last_name)
 10  /

107 rows merged.

You can see that If a match is found, the row is updated to match the row in the employees table. If the row is not found, it is inserted into the tab_new table
SQL> select * from tab_new;

       EID FNAME                LNAME
---------- -------------------- -------------------------
       101 Neena                Kochhar
       102 Lex                  De Haan
       103 Alexander            Hunold
       198 Donald               OConnell
       199 Douglas              Grant
:
:
:
:
107 rows inserted