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_at_perceptron.com> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *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_at_gmail.com> wrote:
> >
> > On 9/28/05, Ranko Mosic <ranko.mosic_at_gmail.com > 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>;
> >
>
>

--
http://www.freelists.org/webpage/oracle-l