RE: merge command ???

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Wed, 28 Jan 2004 13:44:27 -0800
Message-ID: <F001.005DE4C5.20040128134427@fatcity.com>

Janet,

How about something like:
Insert into caption c3
(c3.CAPTION_ID,

        c3.CAPTION_NAME, 
        c3.VISIBILITY_ID, 
        c3.MOD_DATE, 
        c3.MOD_USER)

Select
c1.CAPTION_ID,

        c1.CAPTION_NAME, 
        c1.VISIBILITY_ID, 
        c1.MOD_DATE, 
        c1.MOD_USER

>From caption_at_gtd1 c1

Where c1.caption_id not in(select c3.caption_id from caption);

Depending on the sizes of the tables, and considering the fact that one table is across a database link, you may be able to tune this, but the idea should work.

-Mark

Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is." --Unknown

-----Original Message-----
Sent: Wednesday, January 28, 2004 11:54 AM To: Multiple recipients of list ORACLE-L

Hi, for Merge command, how to specify if matched, not to do anything, I tried NULL, not working. Thank you!

MERGE INTO caption c3
USING caption_at_gtd1 c1
ON (c3.caption_id = c1.caption_id)
WHEN MATCHED THEN
NULL -- don't need to do anything when matched! WHEN NOT MATCHED THEN
INSERT (c3.CAPTION_ID,

        c3.CAPTION_NAME, 
        c3.VISIBILITY_ID, 
        c3.MOD_DATE, 
        c3.MOD_USER) 
 VALUES (c1.CAPTION_ID, 
        c1.CAPTION_NAME, 
        c1.VISIBILITY_ID, 
        c1.MOD_DATE, 
        c1.MOD_USER); 

__________________________________

Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Janet Linsy
  INET: janetlinsy_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bobak, Mark
  INET: Mark.Bobak_at_il.proquest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Jan 28 2004 - 15:44:27 CST