Re: Dimension table load - PLSQL question
- From: Ranko Mosic <ranko.mosic@xxxxxxxxx>
- To: Igor Neyman <ineyman@xxxxxxxxxxxxxx>
- Date: Thu, 29 Sep 2005 10:10:24 -0400
That's for 10g. I am on 9.2 here.
thanks, rm
On 9/29/05, Igor Neyman <ineyman@xxxxxxxxxxxxxx> wrote:
>
> From Oracle docs:
>
> "merge_insert_clause
> .....................................
> .....................................
> You can specify this clause by itself or with the merge_update_clause.
> If you specify both, then they can be in either order."
>
>
> ________________________________
>
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ranko Mosic
> Sent: Thursday, September 29, 2005 9:51 AM
> To: Igor Neyman
> Cc: Paul Drake; ORACLE-L
> Subject: Re: Dimension table load - PLSQL question
>
>
> 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 <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
> <mailto: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 <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>
> <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:
