Re: merge command ???
Can't do it (until version 10) Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearances: Jan 29th 2004 UKOUG Unix SIG - v$ and x$ March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February UK___June The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, January 28, 2004 4:54 PM > 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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: merge command ???
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
