Donald K. Burleson Oracle Tips

Upserts in Oracle8i data warehouses

An “upsert” in the combination of an INSERT and UPDATE statement, built into a single clause.  The Upsert model is especially useful in data warehouses where you need the following logic:

IF FOUND

   THEN UPDATE

ELSE

   INSERT;

Upserts are great for processes that you normally require multiple insert as select statements IAS) statements.  This is because upserts remove the need for row-at-a-time processing and enable the entire transaction as a single set.

Let’s illustrate upserts with a simple example.  Let’s assume that we need to take a NEW_CUSTOMERS table and spread the row into two other tables. The RICH_CUSTOMERS table is populated by selecting only those customers with a credit_limit > 100000, and all tables are moved into the CUSTOMER table.

In Oracle8i, this operation required two statements:

INSERT INTO

   rich_customers

(cust_id,cust_credit_limit)

SELECT cust_id, cust_credit_limit

FROM new_customers

WHERE credit_limit >=100000;

INSERT INTO customers SELECT * FROM new_customers;

In Oracle9i, an UPSERT can accomplish this task in a single statement:

INSERT

FIRST WHEN

   credit_limit >=100000

THEN INTO

   rich_customers

VALUES(cust_id,cust_credit_limit)

   INTO customers

ELSE

   INTO customers SELECT * FROM new_customers;