Merge two tables with update : Merge « Table « Oracle PL/SQL Tutorial

Home
Oracle PL/SQL Tutorial
1.Introduction
2.Query Select
3.Set
4.Insert Update Delete
5.Sequences
6.Table
7.Table Joins
8.View
9.Index
10.SQL Data Types
11.Character String Functions
12.Aggregate Functions
13.Date Timestamp Functions
14.Numerical Math Functions
15.Conversion Functions
16.Analytical Functions
17.Miscellaneous Functions
18.Regular Expressions Functions
19.Statistical Functions
20.Linear Regression Functions
21.PL SQL Data Types
22.PL SQL Statements
23.PL SQL Operators
24.PL SQL Programming
25.Cursor
26.Collections
27.Function Procedure Packages
28.Trigger
29.SQL PLUS Session Environment
30.System Tables Data Dictionary
31.System Packages
32.Object Oriented
33.XML
34.Large Objects
35.Transaction
36.User Privilege
Oracle PL/SQL Tutorial » Table » Merge 
6.5.7.Merge two tables with update
SQL>
SQL> -- create demo table
SQL> create table emp(
  2    ID                 VARCHAR2(BYTE)         NOT NULL,
  3    fname         VARCHAR2(10 BYTE),
  4    lname          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /

Table created.

SQL>
SQL> -- prepare data
SQL> insert into emp(ID,  fname, lname, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ('01','Jason',    'Martin',  to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56'Toronto',  'Programmer')
  3  /

row created.

SQL> insert into emp(ID,  fname, lname, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('02','Alison',   'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78'Vancouver','Tester')
  3  /

row created.

SQL> insert into emp(ID,  fname, lname, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('03','James',    'Smith',   to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78'Vancouver','Tester')
  3  /

row created.

SQL> insert into emp(ID,  fname, lname, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('04','Celia',    'Rice',    to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78'Vancouver','Manager')
  3  /

row created.

SQL> insert into emp(ID,  fname, lname, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('05','Robert',   'Black',   to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78'Vancouver','Tester')
  3  /

row created.

SQL> insert into emp(ID,  fname, lname, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('06','Linda',    'Green',   to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York',  'Tester')
  3  /

row created.

SQL> insert into emp(ID,  fname, lname, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('07','David',    'Larry',   to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York',  'Manager')
  3  /

row created.

SQL> insert into emp(ID,  fname, lname, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('08','James',    'Cat',     to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')
  3  /

row created.

SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select from emp
  2  /

ID   FNAME      LNAME      START_DAT END_DATE      SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
01   Jason      Martin     25-JUL-96 25-JUL-06    1234.56 Toronto
Programmer

02   Alison     Mathews    21-MAR-76 21-FEB-86    6661.78 Vancouver
Tester

03   James      Smith      12-DEC-78 15-MAR-90    6544.78 Vancouver
Tester


ID   FNAME      LNAME      START_DAT END_DATE      SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
04   Celia      Rice       24-OCT-82 21-APR-99    2344.78 Vancouver
Manager

05   Robert     Black      15-JAN-84 08-AUG-98    2334.78 Vancouver
Tester

06   Linda      Green      30-JUL-87 04-JAN-96    4322.78 New York
Tester


ID   FNAME      LNAME      START_DAT END_DATE      SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
07   David      Larry      31-DEC-90 12-FEB-98    7897.78 New York
Manager

08   James      Cat        17-SEP-96 15-APR-02    1232.78 Vancouver
Tester


rows selected.

SQL>
SQL>
SQL> create table new_emp as select from emp;

Table created.

SQL>
SQL> update new_emp set salary = salary +1;

rows updated.

SQL>
SQL> select from new_emp;

ID   FNAME      LNAME      START_DAT END_DATE      SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
01   Jason      Martin     25-JUL-96 25-JUL-06    1235.56 Toronto
Programmer

02   Alison     Mathews    21-MAR-76 21-FEB-86    6662.78 Vancouver
Tester

03   James      Smith      12-DEC-78 15-MAR-90    6545.78 Vancouver
Tester


ID   FNAME      LNAME      START_DAT END_DATE      SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
04   Celia      Rice       24-OCT-82 21-APR-99    2345.78 Vancouver
Manager

05   Robert     Black      15-JAN-84 08-AUG-98    2335.78 Vancouver
Tester

06   Linda      Green      30-JUL-87 04-JAN-96    4323.78 New York
Tester


ID   FNAME      LNAME      START_DAT END_DATE      SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
07   David      Larry      31-DEC-90 12-FEB-98    7898.78 New York
Manager

08   James      Cat        17-SEP-96 15-APR-02    1233.78 Vancouver
Tester


rows selected.

SQL>
SQL> --Merging Rows Using MERGE
SQL> --merge rows from one table into another
SQL>
SQL> MERGE INTO emp e
  2  USING new_emp ne ON (e.id = ne.id)
  3  WHEN MATCHED THEN UPDATE SET e.salary = ne.salary;

rows merged.

SQL>
SQL>
SQL> -- clean the table
SQL> drop table new_emp;

Table dropped.

SQL> drop table emp
  2  /

Table dropped.

SQL>
6.5.Merge
6.5.1.Merge table
6.5.2.MERGE INTO
6.5.3.Merge with match
6.5.4.Merge table into another table
6.5.5.Merge two tables and map columns
6.5.6.Merge two tables when matched or not matched
6.5.7.Merge two tables with update
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.