Thanks for very helpful, no patronizing answer. If you've read more carefully what the problem is you'd see that MERGE can't work because it works on one table upserting another. I have one table being inserted from 6 tables. Thanks genius.
On 9/28/05, Paul Drake <bdbafh@(protected)> wrote: > > On 9/28/05, Ranko Mosic <ranko.mosic@(protected)> wrote: > > > > Hi, > > requirement: > > - input parameters are codes p_cd1, p_cd2, ... > > - for these codes I get descriptions ( select descr1 into v_descr1 from > > lkp_table1 where cd = p_cd1; select descr2 into v_descr2 from lkp_table2 where > > cd = p_cd2 etc ) > > - check if table t has records where t.descr1 = v_descr1 > > and t.descr2 = v_descr2 and on and on ....; > > - if row exists return primary key; > > - if not then insert. > > > > What is the best way of doing it ( simplest ) ? > > > > Regards, Ranko. > > > > > > > Ranko, > > "Simplest way" is to solicit opinions without using a search engine or > checking the documentation. > Its also usually "simplest" to leverage the existing provided > functionality, rather than writing your own routines, error handling, etc. > > A search of "oracle 10.1 upsert" in google.com <http://google.com/> + "I'm > feeling lucky" produced this for me. > Perhaps you might get lucky too. > > Paul > > http://www.psoug.org/reference/merge.html > > > MERGE <hint> INTO <table_name> > USING <table_view_or_query> > ON (<condition>) > WHEN MATCHED THEN <update_clause> > WHEN NOT MATCHED THEN <insert_clause>; >
<div>Thanks for very helpful, no patronizing answer. If you've read more carefully what the problem is</div> <div>you'd see that MERGE can't work because it works on one table upserting another. </div> <div>I have one table being inserted from 6 tables. </div> <div>Thanks genius.<br><br> </div> <div><span class="gmail_quote">On 9/28/05, <b class="gmail_sendername">Paul Drake</b> <<a href="mailto:bdbafh@(protected)">bdbafh@(protected)</a>> wrote: </span> <blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0 .8ex; BORDER-LEFT: #ccc 1px solid"> <div><span class="e" id="q_1069edab88b6ca8a_0">On 9/28/05, <b class="gmail _sendername">Ranko Mosic</b> <<a onclick="return top.js.OpenExtLink(window ,event,this)" href="mailto:ranko.mosic@(protected)" target="_blank">ranko.mosic @(protected) </a>> wrote: <div><span class="gmail_quote"></span> <blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0 .8ex; BORDER-LEFT: rgb(204,204,204) 1px solid"><pre>Hi, <br>requirement: <br>- input parameters are codes p_cd1, p_cd2, ...<br>- for these codes I get descriptions ( select descr1 into v_descr1 from <br>lkp_table1 where cd = p_cd1; select descr2 into v_descr2 from lkp_table2 where <br>cd = p_cd2 etc )<br>- check if table t has records where t.descr1 = v _descr1<br> and t.descr2 = v_descr2 and on and on ....; <br>- if row exists return primary key; <br>- if not then insert. <br><br>What is the best way of doing it ( simplest ) ? <br><br>Regards, Ranko. <br></pre><br></blockquote></div><br></span></div >Ranko,<br><br>"Simplest way" is to solicit opinions without using a search engine or checking the documentation.<br>Its also usually "simplest " to leverage the existing provided functionality, rather than writing your own routines, error handling, etc. <br><br>A search of "oracle 10.1 upsert" in <a onclick="return top.js .OpenExtLink(window,event,this)" href="http://google.com/" target="_blank" >google.com</a> + "I'm feeling lucky" produced this for me. <br>Perhaps you might get lucky too.<br><br>Paul<br><br><a onclick="return top .js.OpenExtLink(window,event,this)" href="http://www.psoug.org/reference/merge .html" target="_blank">http://www.psoug.org/reference/merge.html</a> <br><br> <table width="90%" bgcolor="#d8d8c4" border="1"> <tbody> <tr> <td width="25%" rowspan="2"><br></td> <td width="75%"><font face="Courier">MERGE <hint> INTO <table_name> <br>USING <table_view_or_query><br>ON (<condition>)<br>WHEN MATCHED THEN <update_clause><br>WHEN NOT MATCHED THEN <insert_clause>; </font></td></tr></tbody></table><br></blockquote></div><br>
