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;