Upsert Command

Combination of insert and update operations in a single statement. Rows in the input table are divided into two groups based on a predicate: insert source table if the predicate evaluates to false or unknown and update source table if the predicate evaluates to true. Insert source table is inserted into the target table. Every row in target table that has a matching row in update source table is updated. It is an error if a given row in target table matches with more than one row in update source table.

---

Merge/Upsert Syntax:

  {MERGE | UPSERT} INTO <target-table> [[AS] <id>] 
  USING {<sub-query>|<source-table>} [[AS] <id>] 
  ON ({merge-condition})
  WHEN MATCHED THEN
    UPDATE <table> SET <target-column> = {expression} 
    [, <target-column> = {expression}] 
  WHEN NOT MATCHED THEN
    INSERT (target-column [, target-column]) 
    VALUES(source-column [, source-column])

---

Example:

  MERGE INTO inventory AS in
  USING (SELECT partno, description, count
          FROM  shipment
          WHERE shipment.partno IS NOT NULL) AS sh
  ON (in.partno = sh.partno)
  WHEN MATCHED THEN
     UPDATE SET description = sh.description,
                quantity    = in.quantity + sh.count
  WHEN NOT MATCHED THEN
     INSERT    (partno,    description, quantity)
     VALUES (sh.partno, sh.description, sh.count);

---

AxionDatabase and Oracle9i both support ANSI 2003 style Merge/Upsert.