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 [[AS] ]
USING {|} [[AS] ]
ON ({merge-condition})
WHEN MATCHED THEN
UPDATE SET = {expression}
[, = {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.