Re: merge with INSERT... SELECT -- possible?

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 25 Mar 2007 11:33:34 -0700
Message-ID: <1174847613.360268@bubbleator.drizzle.com>

Serge Rielau wrote:
> foothillbiker wrote:

>> All,
>>
>> platform: 10.2.0.2
>>
>> I'm trying to load data into an external table.  The update portion of
>> MERGE works great.  But when rows are not matched, I need to find data
>> from another table so I would like to use the syntax  INSERT....SELECT
>>
>> (This is a simplication of the actual situation)
>>
>> I'm searching for a way to do something along the lines of
>>
>> MERGE INTO salesperson S USING salespersonexternaltable XTAB
>> ON (S.lastname = XTAB.lastname)
>> WHEN MATCHED THEN UPDATE
>> SET S.salestodate = S.salestodate + XTAB.sales
>> WHEN NOT MATCHED THEN
>> INSERT INTO S (pk, lastname, salestodate, manager)
>> SELECT  salesseq.nextval, x.lastname, x.salestodate, EMP.MANAGER
>> FROM salespersonexternaltable X, EMP
>> WHERE EMP.LASTNAME = X.LASTNAME
>> /
>>
>> However, all examples I've been able to find want use the syntax
>> INSERT INTO table (x, y, z)
>> VALUES (a, b, c)
>>
>> Is there a workaround, or should I just do the UPDATES w/ MERGE and
>> INSERTS separately?

>
> I see two choices:
> 1. Push the join into the USING clause:
> MERGE INTO salesperson S
> USING (SELECT XTAB.pk, XTAB.lastname, XTAB.salestodate, EMP.MANAGER
> FROM salespersonexternaltable XTAB
> LEFT OUTER JOIN EMP ON EMP.LASTNAME = XTAB.LASTNAME)
> ON (S.lastname = XTAB.lastname)
> WHEN MATCHED THEN UPDATE
> SET S.salestodate = S.salestodate + XTAB.sales
> WHEN NOT MATCHED THEN
> INSERT VALUES (salesseq.nextval, lastname, salestodate, MANAGER)
>
> 2. Use a scalar subquery in the VALUES (assuming Oracle supports that).
> Note the word SCALAR. If you need more then one column from EMP that
> turns ugly...:
> MERGE INTO salesperson S USING salespersonexternaltable XTAB
> ON (S.lastname = XTAB.lastname)
> WHEN MATCHED THEN UPDATE
> SET S.salestodate = S.salestodate + XTAB.sales
> WHEN NOT MATCHED THEN
> INSERT VALUES (salesseq.nextval, lastname, salestodate,
> (SELECT EMP.MANAGER
> FROM EMP
> WHERE EMP.LASTNAME = XTAB.LASTNAME))
>
> Cheers
> Serge

Before you get too into it the OP wrote:

"I'm trying to load data into an external table. The update portion of MERGE works great."

I doubt that ... you can not UPDATE with an external table: Only Insert.

I am left wondering what the OP is actually doing.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sun Mar 25 2007 - 13:33:34 CDT