Chapter 8: Changing Table Contents | Oracle Database 10g SQL (Osborne ORACLE Press Series)

In this chapter you ll learn more about changing the contents of tables. Specifically, you ll learn

How to add, modify, and remove rows using the INSERT , UPDATE , and DELETE statements.
That a database transaction may consist of multiple INSERT , UPDATE , and DELETE statements.
How to make the results of your transactions permanent using the COMMIT statement, or undo their results entirely using the ROLLBACK statement.
How an Oracle database can process multiple transactions at the same time.

Adding Rows Using the INSERT Statement

You use the INSERT statement to add rows to a table. You can specify the following information in an INSERT statement:

The table into which the row is to be inserted
A list of columns for which you want to specify column values
A list of values to store in the specified columns

When adding a row, you typically need to supply a value for the primary key and all other columns that are defined as NOT NULL . You don t have to specify values for NULL columns if you don t want to; by default they will be set to null.

You can find out which columns are defined as NOT NULL using the SQL*Plus DESCRIBE command. The following example describes the customers table:

  DESCRIBE customers  Name Null? Type  ----------------------------------------- -------- ------------  CUSTOMER_ID NOT NULL NUMBER(38)  FIRST_NAME NOT NULL VARCHAR2(10)  LAST_NAME NOT NULL VARCHAR2(10)  DOB DATE  PHONE VARCHAR2(12)

As you can see, the customer_id , first_name , and last_name columns are NOT NULL , meaning that you must supply a value for these columns. The dob and phone columns don t require a value ”you could omit the values when adding a row and these columns would be set to null.

The following INSERT statement adds a row to the customers table. Notice that the order of values in the VALUES clause matches the order in which the columns are specified in the column list. Also notice that the statement has three parts : the table name, the column list, and the values to be added.

  INSERT INTO customers (   customer_id, first_name, last_name, dob, phone   )   VALUES (   6, 'Fred', 'Brown', '01-JAN-1970', '800-555-1215'   );  1 row created.

SQL*Plus responds that one row has been created. You can verify this by performing the following SELECT statement:

  SELECT *   FROM customers;  CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE ----------- ---------- ---------- --------- ------------  1 John Brown 01-JAN-65 800-555-1211  2 Cynthia Green 05-FEB-68 800-555-1212  3 Steve White 16-MAR-71 800-555-1213  4 Gail Black 800-555-1214  5 Doreen Blue 20-MAY-70  6 Fred Brown 01-JAN-70 800-555-1215

Notice the new row that has been added to the table.

Omitting the Column List

You may omit the column list when supplying values for every column. For example:

 INSERT INTO customers VALUES (7, 'Jane', 'Green', '01-JAN-1970', '800-555-1216');

When you omit the column list, the order of the values you supply must match the order in which the columns are listed in the output from the DESCRIBE command.

Specifying a Null Value for a Column

You can specify a null value for a column using the NULL keyword. For example, the following INSERT specifies a null value for the dob and phone columns:

 INSERT INTO customers VALUES (8, 'Sophie', 'White', NULL, NULL);

When you view this row using a query, you won t see a value for the dob and phone columns because they ve been set to null values:

  SELECT *   FROM customers   WHERE customer_id = 8;  CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE ----------- ---------- ---------- --------- ------------  8 Sophie White

Notice the dob and phone column values are blank.

Including Single and Double Quotes in a Column Value

You can include a single and double quote in a column value. For example, the following INSER T specifies a last name of O Malley for a new customer; notice the use of two single quotes in the last name after the letter O:

 INSERT INTO customers VALUES (9, 'Kyle', 'O''Malley', NULL, NULL);

The next example specifies the name The Great Gatsby for a new product:

 INSERT INTO products (product_id, product_type_id, name, description, price) VALUES (13, 1, 'The "Great" Gatsby', NULL, 12.99);

Copying Rows from One Table to Another

You can copy rows from one table to another using a query in the place of the column values in the INSERT statement. The number of columns and the column types in the source and destination must match. The following example uses a SELECT to retrieve the first_name and last_nam e columns for customer #1 and supplies those columns to an INSERT statement:

 INSERT INTO customers (customer_id, first_name, last_name) SELECT 10, first_name, last_name FROM customers WHERE customer_id = 1;

Notice that the customer_id for the new row is set to 10.

Note	Oracle9 i introduced the new MERGE statement that allows you to merge rows from one table to another. MERGE is much more flexible than combining an INSERT and a SELECT to copy rows from one table to another. You ll learn about MERGE later in the section Merging Rows Using MERGE.