Not in my test - I tried to omitt "matched" and it won't do. SQL> l 1 MERGE INTO bonuses D 2 USING (SELECT employee_id, salary, department_id FROM employees 3 WHERE department_id = 80) S 4 ON (D.employee_id = S.employee_id) 5 WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus) 6* VALUES (S.employee_id, S.salary*0.1) SQL> / VALUES (S.employee_id, S.salary*0.1) * ERROR at line 6: ORA-00905 (See ORA-00905.ora-code.com): missing keyword rm On 9/29/05, Igor Neyman <ineyman@(protected)> wrote: > > "when matched" clause is optional, so you should be fine without updates. > As for returning PK, regular "INSERT" has "RETURNING" clause, but I don't > see one for "insert" used withing "MERGE". File "enhancement request" with > Oracle :) > > -- ---- ---- ---- ---- ---- -- > *From:* oracle-l-bounce@(protected) [mailto: > oracle-l-bounce@(protected)] *On Behalf Of *Ranko Mosic > *Sent:* Thursday, September 29, 2005 9:34 AM > *To:* Igor Neyman > *Cc:* Paul Drake; ORACLE-L > *Subject:* Re: Dimension table load - PLSQL question > > What about returning key if record exists ? There is no update > happening. > Thanks, rm. > > On 9/29/05, Igor Neyman <ineyman@(protected)> wrote: > > > > Does it make a difference: 1 table or 6? > > MERGE INTO t > > USING (SELECT(select descr1 from lkp_table1 where cd = p_cd1) AS > > v_descr1, > > select descr2 from lkp_table2 where cd = p_cd2) AS v_descr2 , > > .... etc. )from dual) c > > ON (t.descr1 = c.v_descr1 and t.descr2 = c.v_descr2 and ... etc) > > WHEN NOT MATCHED INSERT (t.descr1, t.descr2, ...) > > VALUES (c.v_descr1, c.v_descr2, ...) > > Really, no need to react the way, you did... > > Igor Neyman > > > > -- ---- ---- ---- ---- ---- -- > > *From:* oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] > > *On Behalf Of *Ranko Mosic > > *Sent:* Thursday, September 29, 2005 8:45 AM > > *To:* Paul Drake > > *Cc:* ORACLE-L > > *Subject:* Re: Dimension table load - PLSQL question > > > > 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>Not in my test - I tried to omitt "matched" and it won't do. <br >SQL> l<br> 1 MERGE INTO bonuses D<br> 2 USING (SELECT employee_id, salary, department_id FROM employees<br> 3 WHERE department_id = 80) S <br> 4 ON (D.employee_id = S.employee_id)<br> 5 WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)<br> 6* VALUES (S .employee_id, S.salary*0.1)<br>SQL> /<br>VALUES (S.employee_id, S.salary*0.1) <br> * <br>ERROR at line 6:<br>ORA-00905 (See ORA-00905.ora-code.com): missing keyword</div> <div>rm<br> </div> <div><span class="gmail_quote">On 9/29/05, <b class="gmail_sendername">Igor Neyman</b> <<a href="mailto:ineyman@(protected)">ineyman@(protected)< /a>> wrote:</span> <blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0 .8ex; BORDER-LEFT: #ccc 1px solid"> <div dir="ltr" align="left"><span><font face="Arial" color="#0000ff" size="2"> "when matched" clause is optional, so you should be fine without updates.</font></span></div> <div dir="ltr" align="left"><span><font face="Arial" color="#0000ff" size="2" >As for returning PK, regular "INSERT" has "RETURNING" clause, but I don't see one for "insert" used withing "MERGE ". File "enhancement request" with Oracle :) </font></span></div><br> <div lang="en-us" dir="ltr" align="left"> <hr> <font face="Tahoma" size="2"><span class="q"><b>From:</b> <a onclick="return top.js.OpenExtLink(window,event,this)" href="mailto:oracle-l-bounce@(protected) .org" target="_blank">oracle-l-bounce@(protected)</a> [mailto:<a onclick= "return top.js.OpenExtLink(window,event,this)" href="mailto:oracle-l-bounce @(protected)" target="_blank"> oracle-l-bounce@(protected)</a>] <b>On Behalf Of </b>Ranko Mosic<br></span><b >Sent:</b> Thursday, September 29, 2005 9:34 AM<br><b>To:</b> Igor Neyman<br><b >Cc:</b> Paul Drake; ORACLE-L <div><span class="e" id="q_106a21eab3581a1c_3"><br><b>Subject:</b> Re: Dimension table load - PLSQL question<br></span></div></font><br> </div> <div><span class="e" id="q_106a21eab3581a1c_5"> <div></div> <div>What about returning key if record exists ? There is no update happening. </div> <div> </div> <div>Thanks, rm.<br><br> </div> <div><span class="gmail_quote">On 9/29/05, <b class="gmail_sendername">Igor Neyman</b> <<a onclick="return top.js.OpenExtLink(window,event,this)" href= "mailto:ineyman@(protected)" target="_blank">ineyman@(protected) </a>> wrote:</span> <blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0 .8ex; BORDER-LEFT: #ccc 1px solid"> <div dir="ltr" align="left"><span><font face="Arial" color="#0000ff" size="2" >Does it make a difference: 1 table or 6?</font></span></div> <div dir="ltr" align="left"><span><font face="Arial" color="#0000ff" size="2">< /font></span> </div> <div dir="ltr" align="left"><span><font face="Arial" color="#0000ff" size="2" >MERGE INTO t</font></span></div> <div dir="ltr" align="left"><span> <font face="Arial" color=" #0000ff" size="2">USING (SELECT(<font color="#000000" size="3">select descr1 from lkp_table1 where cd = p_cd1) AS v_descr1, </font></font></span>< /div> <div dir="ltr" align="left"><span><font face="Arial" color="#0000ff" size="2"> <font color="#000000" size="3"> select descr2  ;from lkp_table2 where cd = p_cd2) AS v_descr2 ,</font></font></span> </div> <div dir="ltr" align="left"><span> <font face="Arial"> .... etc. )from dual) c</font></span></div> <div dir="ltr" align="left"><span> <font face="Arial">ON (t.descr1 = c.v_descr1</font> <font face="Arial">and t.descr2 = c.v_descr2 and ... etc)</font></span></div> <div dir="ltr" align="left"><span> WHEN NOT MATCHED INSERT (t.<font face="Arial">descr1</font>, t.<font face="Arial">descr2</font>, ...) </span></div> <div dir="ltr" align="left"><span> VALUES (<font face="Arial">c.v_descr1, c.v_descr2, ...)</font></span></div> <div><font face="Arial" color="#0000ff" size="2"></font> </div> <div><span><font face="Arial" color="#0000ff" size="2">Really, no need to react the way, you did...</font></span></div><span> <div><span><font face="Arial" color="#0000ff" size="2"></font></span> < /div> <div><span><font face="Arial" color="#0000ff" size="2">Igor Neyman</font></span ></div></span> <div><span> <div dir="ltr" align="left"><br> </div> <div lang="en-us" dir="ltr" align="left"> <hr> <font face="Tahoma" size="2"><b>From:</b> <a onclick="return top.js.OpenExtLink (window,event,this)" href="mailto:oracle-l-bounce@(protected)" target="_blank" >oracle-l-bounce@(protected)</a> [mailto:<a onclick="return top.js.OpenExtLink (window,event,this)" href="mailto:oracle-l-bounce@(protected)" target="_blank" > oracle-l-bounce@(protected)</a>] <b>On Behalf Of </b>Ranko Mosic<br><b>Sent: </b> Thursday, September 29, 2005 8:45 AM<br><b>To:</b> Paul Drake<br><b>Cc:</b> ORACLE-L<br><b>Subject:</b> Re: Dimension table load - PLSQL question <br></font><br> </div> <div></div> <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 onclick="return top.js.OpenExtLink(window,event,this)" href= "mailto:bdbafh@(protected)" target="_blank">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>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></span></div>< /blockquote></div><br></span></div></blockquote></div><br>
