 |
 |
 |
 |
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
|
|||
 |
|||
|
|||
 |
|||
|
|||
 |
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;
|
|
Copyright © 1996 - 2009 by
Burleson Enterprises, Inc. All rights reserved.
Oracle® is the registered trademark
of Oracle Corporation.