OraFAQ Forum: SQL & PL/SQL » Thinking of using Merge Statement

Home » SQL & PL/SQL » SQL & PL/SQL » Thinking of using Merge Statement (Oracl 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Thinking of using Merge Statement [message #344240]

Fri, 29 August 2008 03:13

alok416
Messages: 7
Registered: June 2005
Location: India

Junior Member

Hi All,

I have a following table.

scott@KRISH> select * from tdest where rownum <11;

KEYVAL VAL1 VAL2 VAL3
---------- ---------- ---------- ----------
16217 85660 173355 162
892987 85661 47872 892
410272 85662 214701 410
28206 85663 87147 282
325664 85664 70941 325
117442 85665 130523 117
708579 85666 173825 708
718883 85667 66477 718
721754 85668 30263 721
224673 85669 212147 224

scott@KRISH> select * from tsource where rownum <11;

KEYVAL VAL1 VAL2 VAL3
---------- ---------- ---------- ----------
99494 100494 1494 99494
16592 17592 592 16592
34181 35181 181 34181
19086 20086 1086 19086
2790 3790 790 2790
2066 3066 66 2066
40407 41407 407 40407
49904 50904 1904 49904
25886 26886 1886 25886
43770 44770 1770 43770

The tdest table contain 102817 rows and tsource table containt 9517 rows.

Now, I need to write the pl/sql programme, with the following requirement.

it will take the data in the TSOURCE table and
1. insert rows not currently in the TDEST table and
2. update TDEST rows with a matching KEYVAL value with the data in TSOURCE

Also, I need to write the pl/sql programme to maximize the performance and efficiency, at the same time, I need to be carefully rollback my work after every attempt at running my program so that I have a chance to try again

for the following scenario, I am thinking of using merge staement, but since I am not that good, I won't able to incorporate the logic in pl/sql.

Would really appricite, if you could throw light on this.

hare krishna
Alok

Report message to a moderator

Re: Thinking of using Merge Statement [message #344242 is a reply to message #344240]

Fri, 29 August 2008 03:25

dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad

Member

You can look at the examples given here.
This could help you.
http://www.oracle-base.com/articles/10g/MergeEnhancements10g.php

Report message to a moderator

Re: Thinking of using Merge Statement [message #344243 is a reply to message #344240]

Fri, 29 August 2008 03:26

Olivia
Messages: 519
Registered: June 2008

Senior Member

Please follow the Guidelines before posting.

Try..something like this below

MERGE INTO DEST B
      USING SOURCE A 
ON ( B.KEYVAL=A.KEYVAL)
WHEN MATCHED THEN
     UPDATE B.VAL1=A.VAL1,
WHEN NOT MATCHED THEN
     INSERT(B.KEYVAL,..) VALUES(A.KEYVAL,...);

Regards,
Oli

[Updated on: Fri, 29 August 2008 03:30]

Report message to a moderator