Rows are inserted into Tables. Based on certain conditions, row insertion is possible to Views also.
Condition for INSERT to work on Views is that any of the following must not be present in the View.
INSERT INTO channels (channel_id ,channel_desc ,channel_class ,channel_class_id , channel_total ,channel_total_id) VALUES (3 ,'Direct Sales' ,'Direct' ,12 ,'Channel total' ,1); The following inserts one row, channel 5, in the channels table: INSERT INTO channels VALUES (5 ,'Catalog' ,'Indirect' ,13 ,'Channel total' ,1);
Example: Inserting row by copying existing data:
INSERT INTO regions (region_id ,region_name) SELECT region_seq.NEXTVAL , terr_name FROM territories@home_office WHERE class = 'R';
Most INSERT statements are the single-table variety, but Oracle also supports a multiple-table INSERT statement. You most frequently use multitable inserts in data warehouse Extract, Transform, and Load (ETL) routines.
With a multitable insert, you can make a single pass through the source data and load the data into more than one table. By reducing the number of passes through the source data, you can reduce the overall work and thus achieve faster throughput.
If a WHEN condition evaluates to TRUE, the corresponding INTO clause is executed. If no WHEN condition evaluates to TRUE, the ELSE clause is executed. The keyword ALL tells the database to check each WHEN condition. On the other hand, the keyword FIRST tells the database to stop checking WHEN conditions after finding the first TRUE condition.
In the following example, an insurance company has policies for both property and casualty in the policy table, but in their data mart, they break out these policy types into separate fact tables. During the monthly load, new policies are added to both the property_premium_fact and casualty_premium_fact tables. You can use a multitable INSERT to add these rows more efficiently than two separate INSERT statements. The multitable INSERT would look like this:
WHEN policy_type = 'P' THEN
INTO property_premium_fact(policy_id, policy_nbr, premium_amt)
VALUES (property_premium_seq.nextval, policy_number, gross_premium)
WHEN p.policy_type = 'C' THEN
INTO property_premium_fact(policy_id, policy_nbr,premium_amt)
VALUES (property_premium_seq.nextval, policy_number,gross_premium)
SELECT policy_nbr ,gross_premium ,policy_type
WHERE policy_date >= TRUNC(SYSDATE,'MM') - TO_YMINTERVAL('00-01');
UPDATE statement is used to change existing rows in a table.
The column list can be either a single column or a comma-delimited list of columns. A single list of columns lets you assign single values—either literals or from a subquery. The following updates customer XYZ’s phone and fax numbers, and sets their quantity based on their orders:
UPDATE order_rollup r
SET phone = '3125551212'
,fax = '7735551212'
,qty = (SELECT SUM(d.qty) FROM order_details d
WHERE d.customer_id = r.customer_id) WHERE r.customer_id = 'XYZ';
Like the CREATE TABLE and ALTER TABLE statements you saw in Chapter 6, when you use a comma-delimited list of columns, you must enclose them in parentheses. The comma-delimited list lets you assign multiple values from a subquery. The following updates both the quantity and price for customer XYZ for the order they placed on October 1, 2004:
SET (qty, price) = (SELECT SUM(qty), SUM(price) FROM order_details
WHERE customer_id = 'XYZ') WHERE customer_id = 'XYZ'
AND order_period = TO_DATE('01-Oct-2004');
Assigning multiple values from a single subquery can save you from having to perform multiple subqueries, thus improving the efficiency of your SQL.
Examples of a DELETE statement.
The following removes duplicate line_detail_ids. Note that the keyword FROM is not needed.
WHERE clause is optional and if not mentioned, all rows will be deleted.
TRUNCATE statement, an alternative to DELETE is for deleting rows, without generating undo.
As the undo data is not generated, TRUNCATE is faster and efficient.