CODEWITHSUNDEEP
oracle-database
SWilk
SWilk

Reputation: 3446

Oracle Merge statement with conditional insert

I use following statement to insert or update current document versions our customers have signed:

        MERGE INTO schema.table ccv
          USING (select :customer_id as customer_id, :doc_type as doc_type, :version as Version FROM DUAL) n
          ON (ccv.customer_id = n.customer_id and ccv.doc_type = n.doc_type)
        WHEN MATCHED 
        THEN UPDATE 
            set ccv.version = n.version
            DELETE WHERE ccv.version is null            
        WHEN NOT MATCHED 
        THEN INSERT 
            ( customer_id, doc_type, version)
        VALUES
            (:customer_id,:doc_type,:version)

Basically I want to avoid inserting on the same condition when I am deleting with DELETE WHERE statement.

The thing is, that there is three different document types (doc_type), but only one or two might be simulatenously signed.

If a client signed a document, then I want to store it's version, if not, then I do not want a record with that document in database.

So, when the new :version is null I delete the existing row. That works great. The problem is, when there were no documents of that customer stored, then oracle actually inserts a record with version = NULL.

How can I avoid inserting records when :version is null?

Is splitting the merge to separate delete, update and insert statement the only way to do that?

Upvotes: 3

Views: 14512

Answers (1)

Sebas
Sebas

Reputation: 21522

If you're using 10g, you may use conditions for the insert as well:

http://www.oracle-developer.net/display.php?id=310

Rgds.

Upvotes: 3

Related Questions