Ronko, there is no need to be tasty ... you didn't specify that you will be reading from 6 table and inserting into one in your op. did you? Raj On 9/29/05, Ranko Mosic <ranko.mosic@xxxxxxxxx> wrote: > > 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@xxxxxxxxx> wrote: > > > > On 9/28/05, Ranko Mosic <ranko.mosic@xxxxxxxxx > 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>; > > > > -- ------------------------------ select standard_disclaimer from company_requirements where category = 'MANDATORY';