tag:blogger.com,1999:blog-5732889.post9008402951551441446..comments2024-03-20T02:16:52.488-05:00Comments on Blog: Upsert / Merge within the Same Table (Oracle)Unknownnoreply@blogger.comBlogger8125tag:blogger.com,1999:blog-5732889.post-36692021143866280762012-07-21T13:09:32.908-05:002012-07-21T13:09:32.908-05:00http://stackoverflow.com/questions/237327/oracle-h...http://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table<br /><br /><br />MERGE INTO Employee USING dual ON ( "id"=2097153 ) WHEN MATCHED THEN UPDATE SET "last"="smith" , "name"="john" WHEN NOT MATCHED THEN INSERT ("id","last","name") VALUES ( 2097153,"smith", "john" )Ullas Sutariahttps://www.blogger.com/profile/15999765375326140573noreply@blogger.comtag:blogger.com,1999:blog-5732889.post-32021782400790243342012-02-07T11:43:58.802-06:002012-02-07T11:43:58.802-06:00Anonymous, how did you make is work?
I still don&...Anonymous, how did you make is work?<br /><br />I still don't get why select p_settlement_id from dual works.<br /><br />thanks!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5732889.post-71466525966396691732011-12-28T00:39:00.540-06:002011-12-28T00:39:00.540-06:00Pretty nice Paul it works really well..Pretty nice Paul it works really well..Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5732889.post-85079358540494292232011-11-30T07:39:25.921-06:002011-11-30T07:39:25.921-06:00p_settlement_id is most likely a parameter, so yes...p_settlement_id is most likely a parameter, so yes, it works.ciulyhttp://www.ciuly.comnoreply@blogger.comtag:blogger.com,1999:blog-5732889.post-21697426690655429102011-03-01T09:50:02.888-06:002011-03-01T09:50:02.888-06:00Anonymous, that's Paul's code so I can'...Anonymous, that's Paul's code so I can't really comment on whether or not it worked.Jason Vogelhttps://www.blogger.com/profile/12539856508013785897noreply@blogger.comtag:blogger.com,1999:blog-5732889.post-77248402950504827182011-02-27T08:47:46.705-06:002011-02-27T08:47:46.705-06:00> select p_settlement_id EXT_SETTLEMENT_REF_NO,...> select p_settlement_id EXT_SETTLEMENT_REF_NO, 'LOKI' EXT_SETTLEMENT_SYSTEM from dual<br /><br />To me it does not make sense to select p_settlement_id from dual. <br /><br />Did your statement work?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5732889.post-54904851167803665852009-01-22T12:32:00.000-06:002009-01-22T12:32:00.000-06:00Paul, which version of Oracle are using?Paul, which version of Oracle are using?Jasonhttps://www.blogger.com/profile/10859950171794898662noreply@blogger.comtag:blogger.com,1999:blog-5732889.post-84473195316862293862009-01-22T12:06:00.000-06:002009-01-22T12:06:00.000-06:00a merge might be possible on the same table if you...a merge might be possible on the same table if you use dual. e.g. I successfully did the following recently:<BR/><BR/>merge into tcns_load_batch_ext_source target<BR/> using (select p_settlement_id EXT_SETTLEMENT_REF_NO, 'LOKI' EXT_SETTLEMENT_SYSTEM from dual) source <BR/> on ( target.EXT_SETTLEMENT_REF_NO = SOURCE.EXT_SETTLEMENT_REF_NO<BR/> and target.EXT_SETTLEMENT_SYSTEM = SOURCE.EXT_SETTLEMENT_SYSTEM)<BR/> when matched then<BR/> update<BR/> set load_status = 'ER',<BR/> load_status_msg = 'Error!: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||' - SP_COPY_STRIP error! ''Pending'' TCNS_LOAD_BATCH_EXT_SOURCE record not found!'<BR/> when not matched then<BR/> insert (CNS_LOAD_BATCH_EXT_SOURCE_ID, CNS_LOAD_BATCH_ID, EXT_SETTLEMENT_REF_NO, EXT_SETTLEMENT_SYSTEM, LOAD_STATUS, LOAD_STATUS_MSG)<BR/> values (sn_tcns_load_batch_ext_source.nextval,p_cns_load_batch_id,p_settlement_id,'LOKI','ER','SP_COPY_STRIP error! ''Pending'' TCNS_LOAD_BATCH_EXT_SOURCE record not found!');<BR/><BR/>This was a way - in a single statement - for me to upsert based on some parameters coming down the line...Anonymousnoreply@blogger.com