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
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’ ;