Oracle 9i Merge Function

From: Dan White <dwhite_at_icimail.com>
Date: Tue, 7 Aug 2001 19:30:13 -0400
Message-ID: <9kpu4p0qpm@enews4.newsguy.com>

Hello all, I was woundering if the new Merge function in 9i could handle multiple inserts or updates below is what I want to do

MERGE INTO customer_dim cd
USING stg_cust_dim sd

      ON ( cd.time_id < sd.time_id
     AND cd.address_id = sd_address_id)

WHEN MATCHED THEN
UPDATE

        SET expired_flag = 'Y',
                expiration_date= sysdate;

INSERT
(cust_id, cd.name, cd.street1, cd.street2, cd.city, cd.state, cd.zip
VALUES
(cust_seq.NEXTVAL, sd.name, sd.street1,sd.street2,sd.city, sd.state,sd.zip);

WHEN NOT MATCHED
INSERT
(cust_id, cd.name, cd.street1, cd.street2, cd.city, cd.state, cd.zip
VALUES
(cust_seq.NEXTVAL, sd.name, sd.street1,sd.street2,sd.city, sd.state,sd.zip);
commit; Received on Tue Aug 07 2001 - 18:30:13 CDT