Home » SQL & PL/SQL » SQL & PL/SQL » Merge problem (merged)  () 1 Vote
Merge problem (merged) [message #216046] Wed, 24 January 2007 19:00 Go to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Hi, I have a question. I am using MERGE to synchronize data from two different databases. It works great in terms of new records or updated records, but if the record is deleted from a particular table on the remote database (the one I need to merge with)? Is there a way to detect that change and also delete that record from the local database?

Thank you!
Re: Oracle Merge? [message #216047 is a reply to message #216046] Wed, 24 January 2007 19:18 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
I'd just create an AFTER DELETE trigger.
Re: Oracle Merge? [message #216049 is a reply to message #216047] Wed, 24 January 2007 19:29 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
unfortunatelly I cannot create triggers on remote database tables Sad ...is there any other way?
Re: Oracle Merge? [message #216051 is a reply to message #216046] Wed, 24 January 2007 19:34 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
DELETE FROM LOCAL_TABLE WHERE PK_ID IN (SELECT PK_ID FROM LOCAL_TABLE MINUS SELECT PK_ID FROM LOCAL_TABLE@REMOTE);
Re: Oracle Merge? [message #216052 is a reply to message #216049] Wed, 24 January 2007 19:37 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
10g supports DELETE in the MERGE statement, but only if the row is matched. ie. it has to exist in the source with some kind of flag to indicate it needs to be deleted in the target.

All you can do is execute a second (DELETE) statement.

Ross Leishman
Re: Oracle Merge? [message #216060 is a reply to message #216052] Wed, 24 January 2007 20:56 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Too bad we are on 9i...I guess gonna have to use separate DELETE statement....thank you, everyone!
MERGE problem [message #216061 is a reply to message #216046] Wed, 24 January 2007 21:16 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
ok, me again, with MERGE again Smile

I have a table on the remote database that looks as follows:

CREATE TABLE (
SSRLINK_TERM_CODE VARCHAR2(6 BYTE) NOT NULL,
SSRLINK_CRN VARCHAR2(5 BYTE) NOT NULL,
SSRLINK_LINK_CONN VARCHAR2(2 BYTE) NOT NULL,
SSRLINK_ACTIVITY_DATE DATE NOT NULL);

ALTER TABLE SSRLINK ADD (
CONSTRAINT PK_SSRLINK
PRIMARY KEY
(SSRLINK_TERM_CODE, SSRLINK_CRN, SSRLINK_LINK_CONN);

CREATE UNIQUE INDEX PK_SSRLINK ON SSRLINK
(SSRLINK_TERM_CODE, SSRLINK_CRN, SSRLINK_LINK_CONN);

I then replicate this table on my local database with one additional column and the same PK and Unique index as follows:


CREATE TABLE PWS_SSRLINK
(
PWS_SSRLINK_TERM_CODE VARCHAR2(6 BYTE) NOT NULL,
PWS_SSRLINK_CRN VARCHAR2(5 BYTE) NOT NULL,
PWS_SSRLINK_LINK_CONN VARCHAR2(2 BYTE) NOT NULL,
PWS_SSRLINK_ACT_DATE DATE,
PWS_ACT_DATE DATE DEFAULT SYSDATE
)

ALTER TABLE PWS_SSRLINK ADD (
CONSTRAINT PK_PWS_SSRLINK
PRIMARY KEY
(PWS_SSRLINK_TERM_CODE, PWS_SSRLINK_CRN, PWS_SSRLINK_LINK_CONN);

CREATE UNIQUE INDEX PK_PWS_SSRLINK ON PWS_SSRLINK
(PWS_SSRLINK_TERM_CODE, PWS_SSRLINK_CRN, PWS_SSRLINK_LINK_CONN);

MERGE statement looks as follows:

MERGE INTO pws_ssrlink a1
USING (SELECT ssrlink_term_code, ssrlink_crn,
ssrlink_link_conn, ssrlink_activity_date
FROM saturn.ssrlink@PROD.US.ORACLE.COM
WHERE ssrlink_term_code = '200601' )e1
ON ( a1.pws_ssrlink_term_code = e1.ssrlink_term_code
AND a1.pws_ssrlink_crn = e1.ssrlink_crn
AND a1.pws_ssrlink_link_conn = e1.ssrlink_link_conn)
WHEN MATCHED THEN UPDATE SET
a1.pws_ssrlink_act_date = e1.ssrlink_activity_date
WHEN NOT MATCHED THEN
INSERT ( pws_ssrlink_term_code, pws_ssrlink_crn,
pws_ssrlink_link_conn,pws_ssrlink_act_date)
VALUES (e1.ssrlink_term_code, e1.ssrlink_crn,
e1.ssrlink_link_conn, e1.ssrlink_activity_date);

-----------------
This works fine if a record is inserted into a remote database as follows:

INSERT INTO SSRLINK (SSRLINK_TERM_CODE,SSRLINK_CRN,SSRLINK_LINK_CONN,SSRLINK_ACTIVITY_DATE)
VALUES ('200601', '00000', 'A2', sysdate);

SELECT * FROM SSRLINK
WHERE SSRLINK_CRN = '00000';

SSRLINK_TERM_CODE SSRLINK_CRN SSRLINK_LINK_CONN
-------------------------------------------------------
200601 00000 A2

When I perform a merge statement I get a new record in my local table PWS_SSRLINK.

SELECT * FROM PWS_SSRLINK
WHERE PWS_SSRLINK_CRN = '00000'

PWS_SSRLINK_TERM_CODE PWS_SSRLINK_CRN PWS_SSRLINK_LINK_CONN
-------------------------------------------------------
200601 00000 A2



But when I update that same record in the remote database, instead of updating MERGE inserts a new record in my database

UPDATE ssrlink
SET ssrlink_link_conn = 'A1'
where ssrlink_crn = '00000';

SELECT * FROM SSRLINK
WHERE SSRLINK_CRN = '00000';

SSRLINK_TERM_CODE SSRLINK_CRN SSRLINK_LINK_CONN
-------------------------------------------------------
200601 00000 A1


SELECT * FROM PWS_SSRLINK
WHERE PWS_SSRLINK_CRN = '00000'

PWS_SSRLINK_TERM_CODE PWS_SSRLINK_CRN PWS_SSRLINK_LINK_CONN
-------------------------------------------------------
200601 00000 A2
200601 00000 A1

Any ideas why this is happening?

thank you!





Re: MERGE problem [message #216062 is a reply to message #216061] Wed, 24 January 2007 21:36 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
> Any ideas why this is happening?
THIS?
Please ask again in complete unambiguous sentences.
Re: MERGE problem [message #216072 is a reply to message #216061] Wed, 24 January 2007 22:26 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
MERGE INTO pws_ssrlink a1
USING <...> e1
ON ( a1.pws_ssrlink_term_code = e1.ssrlink_term_code
AND a1.pws_ssrlink_crn = e1.ssrlink_crn
AND a1.pws_ssrlink_link_conn = e1.ssrlink_link_conn)
WHEN MATCHED THEN UPDATE <...>
WHEN NOT MATCHED THEN INSERT <...> VALUES <...>;

As you changed (in the remote table) the column used in ON clause, the row is not found in your local table, so WHEN NOT MATCHED is executed. In the other words your UPDATE is interpreted as DELETE of the row followed by INSERT with different condition columns. Run DELETE statement suggested in this thread to get rid of the extra row in your local table.
If you do not want this to happen, do not modify columns used in the ON clause or pick better ones (unique id column would serve well).

[Update: description adjusted]

[Updated on: Wed, 24 January 2007 22:29]

Report message to a moderator

Re: MERGE problem [message #216120 is a reply to message #216061] Thu, 25 January 2007 01:49 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
lotusdeva
ok, me again, with MERGE again Smile

No problem; just, next time continue discussion about the same problem in the original topic.
Re: MERGE problem [message #216191 is a reply to message #216072] Thu, 25 January 2007 08:53 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
The thing is that the columns in the ON clause constitute the COMPOSITE PK on the table.

ALTER TABLE PWS_SSRLINK ADD (
CONSTRAINT PK_PWS_SSRLINK
PRIMARY KEY
(PWS_SSRLINK_TERM_CODE, PWS_SSRLINK_CRN, PWS_SSRLINK_LINK_CONN);

So its already unique.. So UPDATE clause does not apply to the columns that are indicated in the ON clause? Since Oracle inserts a new record whenever these columns are modififed?

Also, when I try to use the DELETE statement suggested above, it does not work the way it supposed to because the PK is COMPOSITE.

So in my REMOTE table I have:

SSRLINK_TERM_CODE SSRLINK_CRN SSRLINK_LINK_CONN
----------------------------------------------------------
200601 00000 A1

In my local table I have:

SSRLINK_TERM_CODE SSRLINK_CRN SSRLINK_LINK_CONN
----------------------------------------------------------
200601 00000 A2


If I execute the following, the above row does not get deleted...

DELETE FROM pws_ssrlink
WHERE (pws_ssrlink_term_code IN
(SELECT pws_ssrlink_term_code FROM pws_ssrlink
MINUS SELECT ssrlink_term_code
FROM ssrlink@PROD.US.ORACLE.COM)
OR pws_ssrlink_crn IN (SELECT pws_ssrlink_crn FROM pws_ssrlink
MINUS SELECT ssrlink_crn
FROM ssrlink@PROD.US.ORACLE.COM)
OR pws_ssrlink_link_conn IN (SELECT pws_ssrlink_link_conn
FROM pws_ssrlink
MINUS SELECT ssrlink_link_conn
FROM ssrlink@PROD.US.ORACLE.COM))

I am not sure what I am doing wrong Sad

Thank you!

[Updated on: Thu, 25 January 2007 09:22]

Report message to a moderator

Re: MERGE problem [message #216197 is a reply to message #216191] Thu, 25 January 2007 09:28 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
It would be true if you would trace changes in the remote table (like a log table for a materialized view).
But it does not work in this way. You MERGE data from remote table to your local table and identify matching rows by your primary key. If you change it the link between remote and local rows is broken. So it is interpreted as delete and insert. Uniqueness does not matter, you match rows with EQUAL primary key values.
How would you differentiate between change of primary key and row insert/delete? Eg. in the remote table you have three new rows (not having PK stored in your local table) and five missing ones (PK in local table does not have opposite in remote table). How would you couple these non-matched rows? Try to make it as efficient as merge on primary key.
Re: Merge problem (merged) [message #216208 is a reply to message #216046] Thu, 25 January 2007 10:42 Go to previous message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
That makes sense, thank you. got the delete Smile

[Updated on: Thu, 25 January 2007 11:33]

Report message to a moderator

Previous Topic: Duplicate Rows..
Next Topic: TO_CHAR(hire_date,'fmDD MONTH YYYY') <> TO_CHAR(hire_date,'DD MONTH YYYY')
Goto Forum:
  


Current Time: Mon Dec 05 09:01:19 CST 2016

Total time taken to generate the page: 0.16071 seconds