Geek asks and answers

Conditional Insertion Oracle Merge Statement

I use the following statement to insert or update versions of the current document signed by our clients:

        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 pasting in the same state when I delete the DELETE WHERE statement.

The fact is that there are three different types of document (doc_type), but only one or two can be simulated.

If the client signed the document, I want to save its version, if not, then I do not need a record with this document in the database.

So, when new: version is null, I delete the existing line. This works great. The problem is that when there were no documents from that client, the oracle actually inserts the record with version = NULL.

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

, - ?

+3

More articles:


All Articles