.NET/ASP.NET/C#/VB.NET PDF Document SDK

Rather than unconditionally inserting or updating all the table rows, you may want to insert or update data only when certain conditions are met. In Oracle Database 10g, the MERGE statement has been enhanced to allow you to conditionally insert or delete data. Now, Oracle allows you to use a WHERE clause in a MERGE statement s UPDATE or INSERT clause to conditionally update or insert data, as shown in Listing 13-9 (note the USING clause in the MERGE statement). Listing 13-9. Using UPDATE and INSERT Clauses in a MERGE Statement SQL> MERGE INTO products p USING product_changes s ON (p.prod_id = s.prod_id) /* Destination table /* Source table /* Search/join condition

free barcode fonts for microsoft office, barcode generator excel freeware, how to make barcodes in excel 2003, how to add barcode font in excel 2010, how to create barcodes in excel 2013 free, create barcode in excel 2013 free, free barcode add in for word and excel, barcode font excel 2003 free, how to print barcode in excel, free barcode for excel 2007,

WHEN MATCHED THEN UPDATE /* Update if join SET p.prod_list_price = s.prod_new_price WHERE p.prod_status <> "EXPIRED" /* Conditional update WHEN NOT MATCHED THEN INSERT /* Insert if not join SET p.prod_list_price = s.prod_new_price WHERE s.prod_status <> "EXPIRED" /* Conditional insert Note that Oracle will skip the INSERT or UPDATE operation if the statement doesn t satisfy the WHERE condition. Both the INSERT and UPDATE operations would occur only if the product is not an expired item (WHERE s.prod_status <> "EXPIRED").

You can now use the MERGE statement with an optional DELETE clause. However, you can t use the DELETE clause independently in a MERGE statement, as with the UPDATE or INSERT clause. You must embed the DELETE statement inside the UPDATE statement. This means that the DELETE statement isn t a global clause, but rather works in the confines of the data affected by the UPDATE clause of the MERGE statement. Listing 13-10 shows how the DELETE clause is embedded within the UPDATE clause. Listing 13-10. Using the DELETE Clause in a MERGE Statement SQL> MERGE INTO products p USING product_changes s ON (p.prod_id = s.prod_id) WHEN MATCHED THEN UPDATE SET p.prod_list_price = s.prod_new_price, p.prod_status = s.prod_new_status DELETE WHERE (p.prod_status = "OLD_ITEM") WHEN NOT MATCHED THEN INSERT (prod_id, prod_list_price, prod_status) VALUES (s.prod_id, s.prod_new_price, s.prod_new_status); This MERGE statement will first update the prod_list_price and the prod_status columns of the products table wherever the join condition is true. The join condition (p.prod_id = s.prod_id) joins the two tables: product (the source table) and product_changes (the destination table). Here are a couple of considerations when using the DELETE statement: The DELETE clause affects only the rows that were updated by the MERGE statement. The MERGE statement will delete only the rows included in the join condition specified by the ON clause. In the example, when you use this MERGE statement, the UPDATE clause fires first, and it may set some of the prod_new_status values to expired. The DELETE clause will then remove all the rows whose prod_new_status value was set to expired by the UPDATE clause. The DELETE clause will not remove any other rows with the expired status, unless they are part of the join defined in the ON clause.

There are a couple of items to keep in mind when using expr. As shown in the examples, single spaces are required between the expr call and its arguments, and between the operators and their arguments. Also, when using the asterisk (*) character for multiplication, the shell will interpret it as a wild card and expand it to the elements in your current directory before attempting to evaluate the expression unless you either escape the asterisk via \* or turn off globbing1 (path and file expansion) using set -f.

Suppose you need to insert data from the source table into several target tables. Additionally, you want this loading to be based on various conditions: if condition A, then load into table X; if condition B, then load into table Y; and so on. Normally, you re forced to write several INSERT statements for inserting from the source into the target tables. If the data were very large, this would slow down the data loading. Alternatively, you could write PL/SQL-based code to do the same thing, but that would also slow down the process.

A type of SQL statement called a multitable insert enables you to do fast conditional loads of data from one source into multiple tables simultaneously. Because it s still a normal SQL statement, you can parallelize the operation to make the operation even faster. Multitable inserts can be either unconditional or conditional. You can also have a multitable insert that is a mix of conditional and unconditional inserts. The structure of the multitable insert varies depending on whether all or only some of the source table s rows are being loaded into the target tables.