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

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Sat, 24 Mar 2007 23:33:49 -0400
Message-ID: <56m8svF2a2u3cU1@mid.individual.net>

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

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Received on Sat Mar 24 2007 - 22:33:49 CDT