Merging Rows Using MERGE

The Oracle9 i 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.