Home » SQL & PL/SQL » SQL & PL/SQL » Thinking of using Merge Statement (Oracl 9i)
Thinking of using Merge Statement [message #344240] Fri, 29 August 2008 03:13 Go to next message
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
Re: Thinking of using Merge Statement [message #344242 is a reply to message #344240] Fri, 29 August 2008 03:25 Go to previous messageGo to next message
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
Re: Thinking of using Merge Statement [message #344243 is a reply to message #344240] Fri, 29 August 2008 03:26 Go to previous messageGo to next message
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

Re: Thinking of using Merge Statement [message #344279 is a reply to message #344242] Fri, 29 August 2008 05:29 Go to previous messageGo to next message
alok416
Messages: 7
Registered: June 2005
Location: India
Junior Member



Thanks, but I need to accompils in pl/sql block.

Thanks
Alok
Re: Thinking of using Merge Statement [message #344283 is a reply to message #344279] Fri, 29 August 2008 05:36 Go to previous messageGo to next message
soni_7
Messages: 33
Registered: July 2005
Member
Hi,

You can right it straight away as it is inside a pl/sql block.

Regards
Soni
Re: Thinking of using Merge Statement [message #344285 is a reply to message #344279] Fri, 29 August 2008 05:37 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but I need to accompils in pl/sql block.

1/ Why?
2/ So do it and show us what you tried.

Regards
Michel
Previous Topic: User defined Exception
Next Topic: Dynamic where condition
Goto Forum:
  


Current Time: Thu Dec 08 02:33:19 CST 2016

Total time taken to generate the page: 0.05351 seconds