SERPland Oracle, PL/SQL, PHP, Autos, Bungalow-Hotels and more …

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

Bookmark It