Page 133
Up to this point, the examples have demonstrated how each execution of an INSERT statement can add a single row to a table. The following examples demonstrate how to perform an INSERT without specifying literal values.
The alternative form of the INSERT statement replaces the list of column values with a SELECT statement in the following way:
INSERT INTO table_name [(column_name[,column_name]...[,column_name])] select_statement
The variables are defined as follows:
table_name is the table in which to insert the row.column_name is a column belonging to table_name.
select_statement is a valid SELECT statement.
Assume that a table is used for storing information about instructors who no longer teach at Flugle College. As part of the centennial celebration at Flugle College, all previous instructors are being honored by being made active instructors.
| NOTE |
Please note that the Inactive_Instructor table does not exist in the sample database. To add the inactive instructors to the Instructor table, use a subquery to select the rows from the Inactive_Instructor table and insert them into the Instructor table as demonstrated in Listing 6.12. |
Listing 6.12. Inserting rows with a subquery.
SQL> insert into Instructor 2 (Instructor_ID, Last_Name, First_Name, Department_ID) 3 select Instructor_ID, Last_Name, First_Name, Department_ID 4 from Inactive_Instructor; 5 rows created.
To use a subquery with an INSERT statement, the number of columns referenced in the
INSERT statement must equal the number of items in the subquery's select list.
Page 134
When you develop a database application, you need data to test the software. Developing a sizeable set of test data can be a tedious task. Fortunately, you can use the INSERT statement to duplicate and increase the size of the test data.
Listing 6.13 illustrates how you can use a subquery to copy the existing rows in a table to the same table. Suppose that the Instructor table initially contains 17 rows. If you perform an INSERT using a subquery that retrieves those 17 rows, your table grows to 34 rows. Of course, you can't successfully perform this insert unless you temporarily drop the primary key. If you perform the same INSERT once more, the Product table ends up with 68 rows. As you can see, the number of rows doubles each time you perform an INSERT.
Listing 6.13. Inserting rows into a table by copying the existing rows in the same table.
SQL> insert into Instructor 2 (Instructor_ID, Department_ID, Last_Name) 3 select Instructor_ID, Department_ID, Last_Name 4 from Instructor; 17 rows created.
| NOTE |
If you use the copying technique to generate test data, the number of rows grows exponentially. If you start with 100 rows, the table will hold 12,800 rows after INSERT is performed seven times. If you don't perform a COMMIT after each INSERT, the rollback segments may not be able to store the uncommitted transaction and Oracle returns an error code of ORA-01653; the tablespace in which the rollback segments tried to allocate more space appears in the error message. |
NEW TERM
The COMMIT statement is used to permanently record all changes that the user has made to the database since the last COMMIT statement was issued or since the beginning of the database session, whichever is later.
NEW TERM
The ROLLBACK statement is used to rescind all changes that the user has made to the database since the last COMMIT statement was issued or since the database session began, whichever is later.
Page 135
If you want to modify existing data in an Oracle database, you need use the UPDATE statement. With this statement, you can update zero or more rows in a table.
Like the INSERT statement, the syntax of the UPDATE statement is far simpler than that of the SELECT statement.
The UPDATE statement has the following syntax:
UPDATE table_name
SET column_name = expression [, column = expression] ...
[, column = expression]
[WHERE condition]
The variables are defined as follows:
table_name is the table to be updated.column_name is a column in the table being updated.
expression is a valid SQL expression.
condition is a valid SQL condition.
As you can see, the UPDATE statement references a single table and assigns an expression to at least one column. The WHERE clause is optional; if an UPDATE statement doesn't contain a WHERE clause, the assignment of a value to a column will be applied to all rows in the table.
As the syntax for the UPDATE statement illustrates, an UPDATE statement can change the value for more than one column in a table. Listing 6.14 shows how an UPDATE statement assigns values to two columns: Position and Telephone.
Listing 6.14. Changing the value of more than one column with an UPDATE.
SQL> update Instructor 2 set 3 Position = `PROFESSOR', 4 Telephone = `8055551212'; 17 rows updated.
Page 136
One way to demonstrate that SQL is set oriented is to look at an UPDATE statement that exchanges the values between two columns.
Listing 6.15 contains a query that selects the values for Instructor_ID, Telephone, and Fax that are currently in the Instructor table. You can swap the telephone and fax numbers in the Instructor table with a single UPDATE statement. You do not need to store the telephone and fax numbers in temporary variables as you would if you were using a programming language to swap these columns. The second query of the Instructor table shows that the swap of the two columns was successful.
Listing 6.15. Illustration of the set-orientation of SQL.
SQL> select Instructor_ID, Telephone, Fax
2 from Instructor
3 order by Instructor_ID;
INSTRUCTOR_ID TELEPHONE FAX
-------------------- ---------- ----------
A612
B331
B391
D201 8055550131 8055559444
D944
E301
E405 8055554455
E491
G331
J505
L391
M101 8055550123 8055550321
R983
S131
T149
W490
Y561 8055550123
17 rows selected.
SQL> update Instructor
2 set
3 Telephone = Fax,
4 Fax = Telephone;
17 rows updated.
SQL> select Instructor_ID, Telephone, Fax
2 from Instructor
3 order by Instructor_ID;