Re: Dimension table load - PLSQL question
- From: Ranko Mosic <ranko.mosic@xxxxxxxxx>
- To: Igor Neyman <ineyman@xxxxxxxxxxxxxx>
- Date: Thu, 29 Sep 2005 09:50:36 -0400
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: missing keyword
rm
On 9/29/05, Igor Neyman <ineyman@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *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@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> > *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@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>;
> > >
> >
> >
>
Other related posts:
