How to : Insert If Not Exist, Update When Exist, Then Returning the Value ?
437457Mar 24 2007 — edited Mar 24 2007Hi all,
I have a table :
CREATE TABLE doc_number ( register VARCHAR2(3),
doc_code VARCHAR2(6), doc_nbr NUMBER(12))
I need to do the following :
- If row already exist, update doc_nbr = doc_nbr+1, then return the last doc_nbr values
- if not exist, inser the row, doc_nbr = 1, also return the last doc_nbr value
I cannot use MERGE because of the Returning Values.
Right now I have this statement :
Update doc_number set doc_nbr = doc_nbr + 1
Where register = pRegsCode and doc_code = pDocCode
Returning TO_CHAR(doc_nbr, '0000000') into vDocNo ;
How to implement the INSERT if NOT EXIST ?
Thank you for your help,
xtanto