إنتقال للمحتوى

  • تسجيل الدخول عبر الفيس بوك تسجيل الدخول عبر تويتر Log In with LinkedIn Log In with Google      تسجيل دخول    
  • إنشاء حساب

صورة
- - - - -

Merging Statement in oracle database 10g


2 رد (ردود) على هذا الموضوع

#1 OracleMan

OracleMan

    مشترك

  • الأعضــاء
  • 152 مشاركة

تاريخ المشاركة 08 August 2004 - 07:46 AM

Have you heard about Merging Statement in oracle 10g?


The Oracle9i database introduced the MERGE statement that allows you to merge rows from one table into another. For example, you might want to merge changes to products listed in one table into the products table.

The store schema contains a table named product_changes that was created using the following CREATE TABLE statement in store_schema.sql:

CREATE TABLE product_changes (
product_id INTEGER
CONSTRAINT prod_changes_pk PRIMARY KEY,
product_type_id INTEGER
CONSTRAINT prod_changes_fk_product_types
REFERENCES product_types(product_type_id),
name VARCHAR2(30) NOT NULL,
description VARCHAR2(50),
price NUMBER(5, 2)
);
The following query shows the product_id, product_type_id, name, and price columns for the rows in the product_changes table:

SELECT product_id, product_type_id, name, price
FROM product_changes;

PRODUCT_ID PRODUCT_TYPE_ID NAME PRICE
---------- --------------- ------------------------------ ----------
1 1 Modern Science 40
2 1 New Chemistry 35
3 1 Supernova 25.99
13 2 Lunar Landing 15.99
14 2 Submarine 15.99
15 2 Airplane 15.99
Let’s say the merge should do the following:

For existing rows with matching product_id values in products and product_changes, update the rows in products with the column values that are listed in product_changes. For example, product #1 has a different price in product_changes from that in products, so product #1’s price must be updated in the products table. Similarly, product #2 has a different name and price and must be updated in the products table. Finally, product #3 has a different product_type_id and must be updated in products.

For new rows in product_changes, insert those new rows into the products table. Products #13, #14, and #15 are new in product_changes and must therefore be inserted into products.

The easiest way to learn how to use the MERGE statement is to see an example. The following example performs the merge as defined in the previous bullet points to merge the changes in product_changes into products:

MERGE INTO products p
USING product_changes pc ON (
p.product_id = pc.product_id
)
WHEN MATCHED THEN
UPDATE
SET
p.product_type_id = pc.product_type_id,
p.name = pc.name,
p.description = pc.description,
p.price = pc.price
WHEN NOT MATCHED THEN
INSERT (
p.product_id, p.product_type_id, p.name,
p.description, p.price
) VALUES (
pc.product_id, pc.product_type_id, pc.name,
pc.description, pc.price
);
Notice the following points about the MERGE statement:

The MERGE INTO clause specifies the name of the table to merge the rows into. In the example, the table to merge rows into is the products table, which has an alias of p that is referenced in the rest of the MERGE statement.

The USING ... ON clause specifies a table join. In the example, the join is made on the product_id columns in the products and product_changes tables; the product_changes table has an alias of pc that is referenced in the rest of the MERGE statement.

The WHEN MATCHED THEN clause specifies the action to take when the USING ... ON clause is satisfied for a row. In the example, the action is an UPDATE statement that sets the product_type_id, name, description, and price columns of the existing row in the products table to the column values for the matching row in the product_changes table.

The WHEN NOT MATCHED clause specifies the action to take when the USING ... ON clause is not satisfied for a row. In the example, the action is an INSERT statement that adds a row to the products table, taking the column values from the row in the product_changes table.

If you run the previous MERGE statement, you’ll see that it reports six rows are merged, which are the rows with product_id values of 1, 2, 3, 13, 14, and 15. The following query retrieves the six merged rows from the products table:

SELECT product_id, product_type_id, name, price
FROM products
WHERE product_id IN (1, 2, 3, 13, 14, 15);

PRODUCT_ID PRODUCT_TYPE_ID NAME PRICE
---------- --------------- ------------------------------ ----------
1 1 Modern Science 40
2 1 New Chemistry 35
3 1 Supernova 25.99
13 2 Lunar Landing 15.99
14 2 Submarine 15.99
15 2 Airplane 15.99
If you compare these rows with those shown in Chapter 1 in the section “The Products Table,” you’ll see the following changes in the rows returned by the previous query:

Product #1 has a new price.

Product #2 has a new name and price.

Product #3 has a new product type ID.

Products #13, #14, and #15 are new.

Now that you’ve seen how to make changes to the contents of tables, let’s move on to database transactions.
"I die even as a camel dies. I die in bed, in shame. May the eyes of cowards never find rest in sleep!"
Last Words of Khalid bin Al-Waleed, Sword of Allah

#2 Mo7B

Mo7B

    مشترك

  • الأعضــاء
  • 86 مشاركة

تاريخ المشاركة 09 August 2004 - 08:49 PM

اثنان لا يتعلمان المتكبر والخجول اما ان فلا متكبر ولا خجول من جهلي

شباب تكفون واحد يترجم

#3 wlahmad

wlahmad

    مشرف سابق وعضو مميز

  • المجموعة الماسية
  • 252 مشاركة

تاريخ المشاركة 10 February 2007 - 10:42 PM

زادك المولى إيمانا وعلما
ونفع بك امة الاسلام