MERGING DATA OF TWO TABLES

Question

I want to write a query which finds the difference between two tables and writes updates or new data into third table. My two tables have identical column names. Third table which captures changes have extra column called comment. I would like to insert the comment whether it is a new row or updated row based on the row modification.

**TABLE1 (BACKUP)**
KEY,FIRST_NAME,LAST_NAME,CITY
1,RAM,KUMAR,INDIA
2,TOM,MOODY,ENGLAND
3,MOHAMMAD,HAFEEZ,PAKISTAN
4,MONIKA,SAM,USA
5,MIKE,PALEDINO,USA

**TABLE2 (CURRENT)**
KEY,FIRST_NAME,LAST_NAME,CITY
1,RAM,KUMAR,USA
2,TOM,MOODY,ENGLAND
3,MOHAMMAD,HAFEEZ,PAKISTAN
4,MONIKA,SAM,INDIA
5,MIKE,PALEDINO,USA
6,MAHELA,JAYA,SL



**TABLE3 (DIFFERENCE FROM TABLE2 TO TABLE1)**
KEY,FIRST_NAME,LAST_NAME,CITY,COMMENT
1,RAM,KUMAR,USA,UPDATE
4,MONIKA,SAM,INDIA,UPDATE
6,MAHELA,JAYA,SL,INSERT

table scripts

DROP TABLE TABLE1;
DROP TABLE TABLE2;
DROP TABLE TABLE3;


CREATE TABLE TABLE1
(
KEY NUMBER,
FIRST_NAME VARCHAR2(100),
LAST_NAME VARCHAR2(100),
CITY VARCHAR2(50)
);
/


CREATE TABLE TABLE2
(
KEY NUMBER,
FIRST_NAME VARCHAR2(100),
LAST_NAME VARCHAR2(100),
CITY VARCHAR2(50)
);
/


CREATE TABLE TABLE3
(
KEY NUMBER,
FIRST_NAME VARCHAR2(100),
LAST_NAME VARCHAR2(100),
CITY VARCHAR2(50),
COMMENTS VARCHAR2(200)
);
/

INSERT ALL 
INTO TABLE1
VALUES(1,'RAM','KUMAR','INDIA')
INTO TABLE1 VALUES(2,'TOM','MOODY','ENGLAND')
INTO TABLE1 VALUES(3,'MOHAMMAD','HAFEEZ','PAKISTAN')
INTO TABLE1 VALUES(4,'MONIKA','SAM','USA')
INTO TABLE1 VALUES(5,'MIKE','PALEDINO','USA')
SELECT 1 FROM DUAL;

/

INSERT ALL 
INTO TABLE2
VALUES(1,'RAM','KUMAR','USA')
INTO TABLE2 VALUES(2,'TOM','MOODY','ENGLAND')
INTO TABLE2 VALUES(3,'MOHAMMAD','HAFEEZ','PAKISTAN')
INTO TABLE2 VALUES(4,'MONIKA','SAM','INDIA')
INTO TABLE2 VALUES(5,'MIKE','PALEDINO','USA')
INTO TABLE2 VALUES(6,'MAHELA','JAYA','SL')

SELECT 1 FROM DUAL;

I was using the merge statement to accomplish the same. but i have hit a roadblock in merge statement , it's rhrowing an error "SQL Error: ORA-00905: missing keyword 00905. 00000 - "missing keyword"" I dont understand where is the error. please help

INSERT INTO TABLE3
SELECT KEY,FIRST_NAME,LAST_NAME,CITY,NULL AS COMMENTS FROM TABLE2
MINUS 
SELECT KEY,FIRST_NAME,LAST_NAME,CITY,NULL AS COMMENTS FROM TABLE1

;


MERGE INTO TABLE3 A
USING TABLE1 B
ON (A.KEY=B.KEY)
WHEN MATCHED THEN 
UPDATE SET A.COMMENTS='UPDATED'
WHEN NOT MATCHED THEN
UPDATE SET A.COMMENTS='INSERTED';

You don't need `;` **and** `/` for regular DDL statements: http://stackoverflow.com/questions/1079949/sql-the-semicolon-or-the-slash/10207695#10207695 — , Mar 28 '14 at 07:18

score 1 · Answer 1 · answered Mar 28 '14 at 07:12

1

There is no such WHEN NOT MATCHED THEN UPDATE clause, you should use WHEN NOT MATCHED THEN INSERT. Refer to MERGE for details.

answered Mar 28 '14 at 07:12

user2018791

1,143
15
29

score 0 · Answer 2 · answered Mar 28 '14 at 07:21

Basically MERGE forces the operation: MATCHED=UPDATE (or DELETE), NOT MATCHED = INSERT. It's in the docs.

You can do what you want but you need two insert statements with different set operators,

For UPDATED:

Insert into table3 
    table1 INTERSECT table2

For INSERTED:

Insert into table3 
    table2 MINUS table1

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

A few assumptions made about the data:

An INSERT event will be a record identified by its key in table2 (current data) that does not have a matching key in the original back-up table: table1.
An UPDATE event is a field that exists in both table1 and table2 for the same KEY but is not the same.
Records which did not change between tables are not to be recorded in table3.

Example Query: Check for Updates

 SELECT UPD_QUERY.NEW_CITY, 'UPDATED' as COMMENTS
   FROM (SELECT CASE WHEN REPLACE(CURR.CITY, BKUP.CITY,'') IS NOT NULL THEN CURR.CITY
                ELSE NULL END as NEW_CITY
           FROM table1 BKUP,  table2  CURR
          WHERE BKUP.KEY = CURR.KEY) UPD_QUERY
  WHERE UPD_QUERY.NEW_CITY is NOT NULL;

Updated City Data

You can repeat this comparison method for the other fields:

      SELECT UPD_QUERY.*
        FROM (SELECT CURR.KEY, 
                CASE WHEN REPLACE(CURR.FIRST_NAME, BKUP.FIRST_NAME,'') IS NOT NULL
                     THEN CURR.FIRST_NAME
                     ELSE NULL END as FIRST_NAME,

                CASE WHEN REPLACE(CURR.LAST_NAME, BKUP.LAST_NAME,'') IS NOT NULL
                     THEN CURR.LAST_NAME
                     ELSE NULL END as LAST_NAME,

                CASE WHEN REPLACE(CURR.CITY, BKUP.CITY,'') IS NOT NULL 
                     THEN CURR.CITY
                     ELSE NULL END as CITY
           
                FROM table1 BKUP, table2 CURR
               WHERE BKUP.KEY = CURR.KEY) UPD_QUERY
     WHERE COALESCE(UPD_QUERY.FIRST_NAME, UPD_QUERY.LAST_NAME, UPD_QUERY.CITY) 
           is NOT NULL;

NOTE: This could get unwieldy very quickly if the number of columns compared are many. Since the target table design (table3) requires not only identification of a change, but the field and its new value are also recorded.

Example Query: Look for Newly Added Records

 SELECT CURR.*, 'INSERTED' as COMMENTS
   FROM table2 CURR, table1 BKUP
  WHERE CURR.KEY = BKUP.KEY(+)
    AND BKUP.KEY is NULL;

Newly Inserted Records

MERGING DATA OF TWO TABLES

3 Answers3

A few assumptions made about the data:

Example Query: Check for Updates

Example Query: Look for Newly Added Records