Hour 5. Manipulating Data

In this hour, you learn the part of SQL known as Data Manipulation Language (DML). DML is the part of SQL that you use to change data and tables in a relational database.

Overview of Data Manipulation

DML is the part of SQL that enables a database user to actually propagate changes among data in a relational database. With DML, the user can populate tables with new data, update existing data in tables, and delete data from tables. Simple database queries can also be performed within a DML command.

The three basic DML commands in SQL are

• INSERT

• UPDATE

• DELETE

The SELECT command, which can be used with DML commands, is discussed in more detail in Hour 7, “Introduction to the Database Query.” The SELECT command is the basic query command that you can use after you have entered data into the database with the INSERT command. So in this hour we concentrate on getting the data into our tables so that we have something interesting to use the SELECT command on.

Populating Tables with New Data

Populating a table with data is simply the process of entering new data into a table, whether through a manual process using individual commands or through batch processes using programs or other related software. Manual population of data refers to data entry via a keyboard. Automated population normally deals with obtaining data from an external data source (such as another database or possibly a flat file) and loading the obtained data into the database.

Many factors can affect what data and how much data can be put into a table when populating tables with data. Some major factors include existing table constraints, the physical table size, column data types, the length of columns, and other integrity constraints, such as primary and foreign keys. The following sections help you learn the basics of inserting new data into a table, in addition to offering some Do’s and Don’ts.

Inserting Data into a Table

Use the INSERT statement to insert new data into a table. There are a few options with the INSERT statement; look at the following basic syntax to begin:

INSERT INTO TABLE_NAME
VALUES ('value1', 'value2', [ NULL ] );

Using this INSERT statement syntax, you must include every column in the specified table in the VALUES list. Notice that each value in this list is separated by a comma. Enclose the values inserted into the table by single quotation marks for character and date/time data types. Single quotation marks are not required for numeric data types or NULL values using the NULL keyword. A value should be present for each column in the table, and those values must be in the same order as the columns are listed in the table. In later sections, you learn how to specify the column ordering, but for now just know that the SQL engine you are working with assumes that you want to enter the data in the same order in which the columns were created.

In the following example, you insert a new record into the PRODUCTS_TBL table.

Here is the table structure:

Click here to view code image

products_tbl

COLUMN Name                     Null?    DATA Type
----------------------------------------------------
PROD_ID                         NOT NULL VARCHAR(10)
PROD_DESC                       NOT NULL VARCHAR(25)
COST                            NOT NULL NUMBER(6,2)

Here is the sample INSERT statement:

INSERT INTO PRODUCTS_TBL
VALUES ('7725','LEATHER GLOVES',24.99);

1 row created.

In this example, three values were inserted into a table with three columns. The inserted values are in the same order as the columns listed in the table. The first two values are inserted using single quotation marks because the data types of the corresponding columns are of character type. The third value’s associated column, COST, is a numeric data type and does not require quotation marks, although you can use them without fear of affecting the outcome of the statement.

Inserting Data into Limited Columns of a Table

There is a way you can insert data into specified columns. For instance, suppose you want to insert all values for an employee except a pager number. You must, in this case, specify a column list as well as a VALUES list in your INSERT statement.

Click here to view code image

INSERT INTO EMPLOYEE_TBL
(EMP_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, ADDRESS, CITY, STATE, ZIP, PHONE)
VALUES
('123456789', 'SMITH', 'JOHN', 'JAY', '12 BEACON CT',
'INDIANAPOLIS', 'IN', '46222', '3172996868'),

1 row created.

The syntax for inserting values into a limited number of columns in a table is as follows:

INSERT INTO TABLE_NAME ('COLUMN1', 'COLUMN2')
VALUES ('VALUE1', 'VALUE2'),

You use ORDERS_TBL and insert values into only specified columns in the following example.

Here is the table structure:

Click here to view code image

ORDERS_TBL

COLUMN NAME                     Null?    DATA TYPE
-----------------------------------------------------
ORD_NUM                         NOT NULL VARCHAR2(10)
CUST_ID                         NOT NULL VARCHAR2(10)
PROD_ID                         NOT NULL VARCHAR2(10)
QTY                             NOT NULL NUMBER(4)
ORD_DATE                                NULL  DATE

Here is the sample INSERT statement:

insert into orders_tbl (ord_num,cust_id,prod_id,qty)
values ('23A16','109','7725',2);

1 row created.

You have specified a column list enclosed by parentheses after the table name in the INSERT statement. You have listed all columns into which you want to insert data. ORD_DATE is the only excluded column. If you look at the table definition, you can see that ORD_DATE does not require data for every record in the table. You know that ORD_DATE does not require data because NOT NULL is not specified in the table definition. NOT NULL tells us that NULL values are not allowed in the column. Furthermore, the list of values must appear in the same order as the column list.

Inserting Data from Another Table

You can insert data into a table based on the results of a query from another table using a combination of the INSERT statement and the SELECT statement. Briefly, a query is an inquiry to the database that either expects or does not expect data to be returned. See Hour 7 for more information on queries. A query is a question that the user asks the database, and the data returned is the answer. In the case of combining the INSERT statement with the SELECT statement, you are able to insert the data retrieved from a query into a table.

The syntax for inserting data from another table is

Click here to view code image

insert into table_name [('column1', 'column2')]
select [*|('column1', 'column2')]
from table_name
[where condition(s)];

You see three new keywords in this syntax, which are covered here briefly. These keywords are SELECT, FROM, and WHERE. SELECT is the main command used to initiate a query in SQL. FROM is a clause in the query that specifies the names of tables in which the target data should be found. The WHERE clause, also part of the query, is places conditions on the query. A condition is a way of placing criteria on data affected by an SQL statement. A sample condition might state this: WHERE NAME = 'SMITH'. These three keywords are covered extensively during Hour 7 and Hour 8, “Using Operators to Categorize Data.”

The following example uses a simple query to view all data in the PRODUCTS_TBL table. SELECT * tells the database server that you want information on all columns of the table. Because no WHERE clause is used, you see all records in the table as well.

Click here to view code image

select * from products_tbl;
PROD_ID    PROD_DESC                       COST
-----------------------------------------------
11235      WITCH COSTUME                  29.99
222        PLASTIC PUMPKIN 18 INCH         7.75
13         FALSE PARAFFIN TEETH            1.1
90         LIGHTED LANTERNS               14.5
15         ASSORTED COSTUMES              10
9          CANDY CORN                      1.35
6          PUMPKIN CANDY                   1.45
87         PLASTIC SPIDERS                 1.05
119        ASSORTED MASKS                  4.95
1234       KEY CHAIN                       5.95
2345       OAK BOOKSHELF                  59.99

11 rows selected.

Now insert values into the PRODUCTS_TMP table based on the preceding query. You can see that 11 rows are created in the temporary table.

insert into products_tmp
select * from products_tbl;

11 rows created.

You must ensure that the columns returned from the SELECT query are in the same order as the columns that you have in your table or INSERT statement. Additionally, double-check that the data from the SELECT query is compatible with the data type of the column that it is inserting into the table. For example, trying to insert a VARCHAR field with 'ABC’ into a numeric column would cause your statement to fail.

The following query shows all data in the PRODUCTS_TMP table that you just inserted:

Click here to view code image

select * from products_tmp;
PROD_ID    PROD_DESC                       COST
-----------------------------------------------
11235      WITCH COSTUME                  29.99
222        PLASTIC PUMPKIN 18 INCH         7.75
13         FALSE PARAFFIN TEETH            1.1
90         LIGHTED LANTERNS               14.5
15         ASSORTED COSTUMES              10
9          CANDY CORN                      1.35
6          PUMPKIN CANDY                   1.45
87         PLASTIC SPIDERS                 1.05
119        ASSORTED MASKS                  4.95
1234       KEY CHAIN                       5.95
2345       OAK BOOKSHELF                  59.99

11 rows selected.

Inserting NULL Values

Inserting a NULL value into a column of a table is a simple matter. You might want to insert a NULL value into a column if the value of the column in question is unknown. For instance, not every person carries a pager, so it would be inaccurate to enter an erroneous pager number—not to mention, you would not be budgeting space. You can insert a NULL value into a column of a table using the keyword NULL.

The syntax for inserting a NULL value follows:

insert into schema.table_name values
('column1', NULL, 'column3'),

Use the NULL keyword in the proper sequence of the associated column that exists in the table. That column does not have data in it for that row if you enter NULL. In the syntax, a NULL value is being entered in the place of COLUMN2.

Study the two following examples:

Click here to view code image

insert into orders_tbl (ord_num,cust_id,prod_id,qty,ORD_DATE)
values ('23A16','109','7725',2,NULL);

1 row created.

In this example, all columns in which to insert values are listed, which also happen to be every column in the ORDERS_TBL table. You insert a NULL value for the ORD_DATE column, meaning that you either do not know the order date, or there is no order date at this time. Now look at the second example:

insert into orders_tbl
values ('23A16','109','7725',2);

1 row created.

The second example contains two differences from the first statement, but the results are the same. First, there is not a column list. Remember that a column list is not required if you are inserting data into all columns of a table. Second, instead of inserting the value NULL into the ORD_DATE column, you simply leave off the last value, which signifies that a NULL value should be added. Remember that a NULL value signifies an absence of value from a field and is different from an empty string.

Lastly, consider an example where our PRODUCTS_TBL table allowed NULL values and you wanted to insert values into the PRODUCTS_TMP table using it:

Click here to view code image

select * from products_tb;l
PROD_ID    PROD_DESC                       COST
-----------------------------------------------
11235      WITCH COSTUME                  29.99
222        PLASTIC PUMPKIN 18 INCH         7.75
13         FALSE PARAFFIN TEETH            1.1
90         LIGHTED LANTERNS               14.5
15         ASSORTED COSTUMES              10
9          CANDY CORN                      1.35
6          PUMPKIN CANDY                   1.45
87         PLASTIC SPIDERS                 1.05
119        ASSORTED MASKS                  4.95
1234       NULL                            5.95
2345       OAK BOOKSHELF                  59.99
11 rows selected.

insert into products_tmp
select * from products_tbl;

11 rows created.

In this case the NULL values would be inserted without intervention needed on your part as long as the column that the data is being inserted into allowed NULL values. Later this book addresses the need to specify a DEFAULT value for a column that allows you to automatically substitute a value for any NULLs that are inserted.

Updating Existing Data

You can modify pre-existing data in a table using the UPDATE command. This command does not add new records to a table, nor does it remove records—UPDATE simply updates existing data. The update is generally used to update one table at a time in a database, but you can use it to update multiple columns of a table at the same time. An individual row of data in a table can be updated, or numerous rows of data can be updated in a single statement, depending on what’s needed.

Updating the Value of a Single Column

The most simple form of the UPDATE statement is its use to update a single column in a table. Either a single row of data or numerous records can be updated when updating a single column in a table.

The syntax for updating a single column follows:

update table_name
set column_name = 'value'
[where condition];

The following example updates the QTY column in the ORDERS_TBL table to the new value 1 for the ORD_NUM 23A16, which you have specified using the WHERE clause:

update orders_tbl
set qty = 1
where ord_num = '23A16';

1 row updated.

The following example is identical to the previous example, except for the absence of the WHERE clause:

update orders_tbl
set qty = 1;

11 rows updated.

Notice that in this example, 11 rows of data were updated. You set the QTY to 1, which updated the quantity column in the ORDERS_TBL table for all rows of data. Is this really what you wanted to do? Perhaps in some cases, but rarely do you issue an UPDATE statement without a WHERE clause. An easy way to check to see whether you are going to be updating the correct dataset is to write a SELECT statement for the same table with your WHERE clause that you are using in the INSERT statement. Then you can physically verify that these are the rows you want to update.

Updating Multiple Columns in One or More Records

Next, you see how to update multiple columns with a single UPDATE statement. Study the following syntax:

update table_name
set column1 = 'value',
   [column2 = 'value',]
   [column3 = 'value']
[where condition];

Notice the use of the SET in this syntax—there is only one SET, but multiple columns. Each column is separated by a comma. You should start to see a trend in SQL. The comma usually separates different types of arguments in SQL statements. In the following code, a comma separates the two columns being updated. Again, the WHERE clause is optional, but it’s usually necessary.

update orders_tbl
set qty = 1,
    cust_id = '221'
where ord_num = '23A16';

1 row updated.

Later in this book you learn how to write more complex statements so you can update values in one table using values from one or more outside tables through a construct known as a JOIN.

Deleting Data from Tables

The DELETE command removes entire rows of data from a table. It does not remove values from specific columns; a full record, including all columns, is removed. Use the DELETE statement with caution—because it works all too well.

To delete a single record or selected records from a table, use the DELETE statement with the following syntax:

delete from table_name
[where condition];

delete from orders_tbl
where ord_num = '23A16';

1 row deleted.

Notice the use of the WHERE clause. It is an essential part of the DELETE statement if you are attempting to remove selected rows of data from a table. You rarely issue a DELETE statement without the use of the WHERE clause. If you do, your results are similar to the following example:

delete from orders_tbl;

11 rows deleted.

The temporary table that was populated from the original table earlier in this hour can be useful for testing the DELETE and UPDATE commands before issuing them against the original table. Also, remember the technique discussed earlier when we talked about the UPDATE command. Write a SELECT statement using the same WHERE clause that you are attempting to use for the DELETE statement. That way you can verify that the data being deleted is actually the data you want.

Summary

You have learned the three basic commands in DML: the INSERT, UPDATE, and DELETE statements. As you have seen, data manipulation is a powerful part of SQL, allowing the database user to populate tables with new data, update existing data, and delete data.

An important lesson when updating or deleting data from tables in a database is sometimes learned when neglecting the use of the WHERE clause. Remember that the WHERE clause places conditions on an SQL statement—particularly in the case of UDPATE and DELETE operations, when you are specifying specific rows of data that are affected during a transaction. All target table data rows are affected if the WHERE clause is not used, which could be disastrous to the database. Protect your data, and be cautious during data manipulation operations.

Q&A

Q. With all the warnings about DELETE and UPDATE, I’m a little afraid to use them. If I accidentally update all the records in a table because I didn’t use the WHERE clause, can I reverse the changes?

A. There is no reason to be afraid, because there is not much you can do to the database that cannot be corrected, although considerable time and work might be involved. Hour 6, “Managing Database Transactions,” discusses the concepts of transactional control, which allows data manipulation operations to be finalized or undone.

Q. Is the INSERT statement the only way to enter data into a table?

A. No, but remember that the INSERT statement is ANSI standard. The various implementations have their tools to enter data into tables. For example, Oracle has a utility called SQL*Loader. Also, many of the various implementations have utilities called IMPORT that can insert data. There are many good books on the market that expand on these utilities.

Workshop

The following workshop is composed of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises are intended to afford you the opportunity to apply the concepts discussed during the current hour, as well as build upon the knowledge acquired in previous hours of study. Please take time to complete the quiz questions and exercises before continuing. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.

Quiz

1. Use the EMPLOYEE_TBL with the following structure:

Click here to view code image

Column        data type     (not)null
last_name     varchar2(20)   not null
first_name    varchar2(20)   not null
ssn           char(9)        not null
phone         number(10)     null

LAST_NAME    FIRST_NAME      SSN           PHONE
SMITH        JOHN            312456788     3174549923
ROBERTS      LISA            232118857     3175452321
SMITH        SUE             443221989     3178398712
PIERCE       BILLY           310239856     3176763990

What would happen if the following statements were run?

a. insert into employee_tbl
('JACKSON', 'STEVE', '313546078', '3178523443'),

b. insert into employee_tbl values
('JACKSON', 'STEVE', '313546078', '3178523443'),

c. insert into employee_tbl values
('MILLER', 'DANIEL', '230980012', NULL);

d. insert into employee_tbl values
('TAYLOR', NULL, '445761212', '3179221331'),

e. delete from employee_tbl;

f. delete from employee_tbl
where last_name = 'SMITH';

g. delete from employee_tbl
where last_name = 'SMITH'
and first_name = 'JOHN';

h. update employee_tbl
set last_name = 'CONRAD';

i. update employee_tbl
set last_name = 'CONRAD'
where last_name = 'SMITH';

j. update employee_tbl
set last_name = 'CONRAD',
first_name = 'LARRY';

k. update employee_tbl
set last_name = 'CONRAD'
first_name = 'LARRY'
where ssn = '313546078';

Exercises

1. Go to Appendix E, “INSERT Statements for Data in Book Examples.” Invoke your RDBMS query editor as you have done in previous exercises.

Now you need to insert the data into the tables that you created in Hour 3, “Managing Database Objects.” Carefully type and execute each of the INSERT statements in Appendix E to populate your tables. After you have executed all the commands for this hour in Appendix E, your tables will be populated with data, and you can proceed with the exercises in the rest of this book.

2. Use the PRODUCTS_TBL for this exercise.

Add the following products to the product table:

Click here to view code image

PROD_ID       PROD_DESC                COST
301           FIREMAN COSTUME          24.99
302           POLICEMAN COSTUME        24.99
303           KIDDIE GRAB BAG           4.99

Write DML to correct the cost of the two costumes added. The cost should be the same as the witch costume.

Now we have decided to cut our product line, starting with the new products. Remove the three products you just added.

Before you executed the statements to remove the products you added, what should you have done to ensure that you only delete the desired rows?