• Describe data manipulation using INSERT, UPDATE, and DELETE statements.
Focused Explanation
Data Manipulation Language (DML) statements are used to manipulate data in the database. There are three DML statements, INSERT, UPDATE, and DELETE.
Using the INSERT Statement
You can use the INSERT statement to add rows of data with specific values to a table and to copy the rows from an existing table into a new table using a subquery. The INSERT statement can also be used for inserting rows into a single table or into multiple tables. The following is the syntax to use the INSERT statement for adding records to a single table:
INSERT INTO table_name[(column1,column2,column3...columnn)]
VALUES(value1,value2,...valuen);
You can also insert records into a view, which inserts data into the underlying base tables, if the view does not contain the following values:
• A DISTINCT operator
• A set operator, such as UNION or MINUS
• An aggregate function, such as SUM, COUNT, or AVG
• A GROUP BY, ORDER BY, or CONNECT BY clause
• A subquery in the SELECT list
For example, you can use the following INSERT statement to insert records into the employees table:
SQL> INSERT INTO employees (employee_id, employee_name, employee_dept, sal) VALUES (222, ‘Jorge’, ‘Mkt.’, 2000);
Note: In the INSERT statement, if you insert values in all the columns of a table, you do not need to include the name of the columns in which you want to insert values.
You can also use the INSERT statement to copy records from one table to another. An example of copying one or more rows from a table to another table is:
SQL> INSERT INTO regions (region_id, region_name) 2 VALUES (SELECT region_seq.NEXTVAL, terr_name 3 FROM territories@home_office WHERE class = ‘R’);
The number and data types of the values inserted in the VALUES list must match the number and data types in the column list. The database implicitly converts the data type of each value to be copied from the source table to the data type of the target table.
Inserting Data into Multiple Tables
Oracle also supports a multi-table INSERT statement to simultaneously insert records into multiple tables.
You can use this statement to make a single pass through the source data and load the data into more than one table. You can achieve faster throughput by reducing the number of passes through the source data.
For example, an insurance company has both property and casualty policies stored in a table named policies. In the data mart, these policies are represented in two separate tables,
property_premium_fact and casuality_premium_fact. Here, you can use a multi-table INSERT to add the records to database tables more efficiently than using two separate INSERT statements. The following statements show how to use the multi-table INSERT statement:
SQL>INSERT FIRST
2 WHEN policy_type = ‘P’ THEN
3 INTO property_premium_fact (policy_id, policy_nbr, premium_amt) 4 VALUES (property_premium_seq.nextval, policy_number, gross_premium) 5 WHEN policy_type = ‘C’ THEN
6 INTO casuality_premium_fact (policy_id, policy_nbr, premium_amt)
7 VALUES (property_premium_seq.nextval, policy_number, gross_premium) 8 SELECT policy_number, gross_premium, policy_type
9 FROM policies
10 WHERE policy_date >= TRUNC(SYSDATE,’MM’) – TO_YMINTERVAL(’00-01’);
In this statement, records are retrieved from the policies table and depending on the value of the policy_type column, retrieved data is inserted into either the property_premium_fact table or the casuality_premium_fact table. When using a multi-table INSERT statement instead of two separate INSERT statements, the code makes a single pass through the policies table, instead of two different passes and loads the data faster.
Using the UPDATE Statement
The UPDATE statement is used to change the existing records in a table. The syntax of the UPDATE statement is:
UPDATE table_name SET column_name=value;
The SET clause can be a list of columns, with their corresponding values, separated by commas.
The following example updates the phone number and fax number of a customer whose customer ID is 302 and sets the quantity based on the customer’s orders in the order_details table:
SQL> UPDATE order_table r SET phone = ‘8277588800’, 2 fax = ‘6789698999’,
3 qty = (SELECT SUM(d.qty) 4 FROM order_details d
5 WHERE d.customer_id = r.customer_id) 6 WHERE r.customer_id = 302;
These statements update the values in the phone, fax, and qty columns of the order_table table.
The value assigned to the qty column is derived from a subquery. This subquery returns the sum of the values in the qty column of the order_details table, where the value in the customer_id column of the order_details column is equal to the value in the customer_id column of the order_table table.
Using the DELETE Statement
The DELETE statement is used to remove records from a table. The syntax for the DELETE statement is:
DELETE FROM table_name WHERE column_name=value;
You can delete only those rows that satisfy a particular condition, by specifying the condition in the WHERE clause of the DELETE statement.
For example, you can use the following DELETE statement to remove the customer orders from the orders table for specified values of the state and order_date fields:
SQL> DELETE FROM orders
2 WHERE state IN (‘TX’, ‘NY’, ‘IL’)
3 AND order_date < TRUNC(SYSDATE) – 90;
This statement deletes those rows from the orders table for which the state column contains the TX, NY, or IL value and the order_date column contains a value older than 90 days from the current date.
The following example removes rows from the line_detail table that have duplicate values of the detail_id column in the detail table:
SQL> DELETE FROM line_detail
2 WHERE rowid NOT IN (SELECT MAX(rowid) 3 FROM detail
4 GROUP BY detail_id);
When the WHERE clause is not used, all the records from the table are deleted. You can also use the TRUNCATE TABLE statement to delete all the records from a table. However, you cannot roll back the TRUNCATE TABLE statement.