Oracle Merge statement with conditional insert

oracle

Solution

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

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

Rgds.

Problem

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?

Original source