SERPland Flugverkehr/Airtraffic Zürich – Auto Direktimport Dänemark – Disneyland Paris – Legoland Deutschland

5Oct/081

PL/SQL: Merge Statement – Merge into a single table (no Join to insert or update) – like mySQL upsert

On Oracle, you can write simple insert/update statements with PL/SQL. Also using some if-then-else cases you can decide wether to insert or to update data.

The much more elegant way to insert or update data with pl/sql is to use Oracle's MERGE Command (like upsert in mySQL).

Usually within the Merge command, you can join two tables together to check the data. Just search the web - you'll find many examples with MERGE and JOINS.

But what when you'd only like to MERGE into a single table?

After some tries, I found a solution using the DUAL table.
Just see my MERGE PL/SQL sample. It's absolutelly easy to understand:

merge into position_table t
using dual
on (t.pos_id = p_pos_id)
when matched then
update
set anzahl = p_anzahl
, gueltig_ab = p_gueltig_ab
, doc_id = p_doc_id
, bemerkung = p_bemerkung
when not matched then
insert
(pos_id
,anzahl
,gueltig_ab
,doc_id
,bemerkung
)
values
(p_pos_id
,p_anzahl
,p_gueltig_ab
,p_doc_id
,p_bemerkung
)
; -- ende merge

Related Posts:

Comments (1) Trackbacks (0)
  1. merge into ACCOUNT_MASTER e
    using dual on (dual.dummy is null and e.account_type=’Masd’ and e.code= ‘dfdf’ )
    when noT matched then
    INSERT
    VALUES(‘DLR211′,’00000066009304′,’mar’,’00437′,’Margin’,sysdate,’ADD’,’mega’)
    when matched then
    update set e.ACCOUNT_NO=’0000sdsd19304′, e.ACCOUNT_NICKNAME=’mar’, e.BRANCH_CODE=’wewe37′,
    e.LAST_MOD_TIME=sysdate, e.ACTION=’EDIT’, e.MODIFIED_BY=’mega’ ;


Leave a comment

No trackbacks yet.