- We have to issue at least two statements
- We have to think about performance
- We have to think about race conditions
- We have to choose between [UPDATE; IF UPDATE_COUNT = 0 THEN INSERT] and [INSERT; IF EXCEPTION THEN UPDATE]
- We have to do those statements once per updated / inserted record
A typical situation for MERGEAmong many other use-cases, the MERGE statement may come in handy when handling many-to-many relationships. Let’s say we have this schema:
The above tables are used to model which person has read (flag=1) / deleted (flag=2) what document. To make things simple, the “document_person” entity is usually OUTER JOINed to “documents”, such that the presence or absence of a “document-person” record may have the same semantics: “flag IS NULL” means the document is unread. Now when you want to mark a document as read, you have to decide whether you INSERT a new “document_person”, or whether to UPDATE the existing one. Same with deletion. Same with marking all documents as read, or deleting all documents.
CREATE TABLE documents ( id NUMBER(7) NOT NULL, CONSTRAINT docu_id PRIMARY KEY (id) ); CREATE TABLE persons ( id NUMBER(7) NOT NULL, CONSTRAINT pers_id PRIMARY KEY (id) ); CREATE TABLE document_person ( docu_id NUMBER(7) NOT NULL, pers_id NUMBER(7) NOT NULL, flag NUMBER(1) NULL, CONSTRAINT docu_pers_pk PRIMARY KEY (docu_id, pers_id), CONSTRAINT docu_pers_fk_docu FOREIGN KEY (docu_id) REFERENCES documents(id), CONSTRAINT docu_pers_fk_pers FOREIGN KEY (pers_id) REFERENCES persons(id) );
Use MERGE insteadYou can do it all in one statement! Let’s say, you want to INSERT/UPDATE one record, in order to mark one document as read for a person:
This looks quite similar, yet incredibly more verbose than MySQL’s INSERT .. ON DUPLICATE KEY UPDATE statement, which is a bit more concise.
-- The target table MERGE INTO document_person dst -- The data source. In this case, just a dummy record USING ( SELECT :docu_id as docu_id, :pers_id as pers_id, :flag as flag FROM DUAL ) src -- The merge condition (if true, then update, else insert) ON (dst.docu_id = src.docu_id AND dst.pers_id = src.pers_id) -- The update action WHEN MATCHED THEN UPDATE SET dst.flag = src.flag -- The insert action WHEN NOT MATCHED THEN INSERT ( dst.docu_id, dst.pers_id, dst.flag ) VALUES ( src.docu_id, src.pers_id, src.flag )
Taking it to the extremeBut you can go further! As I said previously, you may also want to mark ALL documents as read, for a given person. No problem with MERGE. The following statement does the same as the previous one, if you specify :docu_id. If you leave it null, it will just mark all documents as :flag:
MERGE INTO document_person dst -- The data source is now all "documents" (or just :docu_id) left outer -- joined with the "document_person" mapping USING ( SELECT d.id as docu_id, :pers_id as pers_id, :flag as flag FROM documents d LEFT OUTER JOIN document_person d_p ON d.id = d_p.docu_id AND d_p.pers_id = :pers_id -- If :docu_id is set, select only that document WHERE (:docu_id IS NOT NULL AND d.id = :docu_id) -- Otherwise, select all documents OR (:docu_id IS NULL) ) src -- If the mapping already exists, update. Else, insert ON (dst.docu_id = src.docu_id AND dst.pers_id = src.pers_id) -- The rest stays the same WHEN MATCHED THEN UPDATE SET dst.flag = src.flag WHEN NOT MATCHED THEN INSERT ( dst.docu_id, dst.pers_id, dst.flag ) VALUES ( src.docu_id, src.pers_id, src.flag )