Statements That Modify Data
The following statements modify data:
Although these SQL statements are relatively simple when compared with the more advanced SELECT statements, use them carefully because they change the contents of the database.
Deleting Rows
The DELETE statement removes any row or combination of rows from a table. You cannot recover a deleted row after the transaction is committed. (Transactions are discussed under "Interrupted Modifications". For now, think of a transaction and a statement as the same thing.)
When you delete a row, you must also be careful to delete any rows of other tables whose values depend on the deleted row. If your database enforces referential constraints, you can use the ON DELETE CASCADE option of the CREATE TABLE or ALTER TABLE statements to allow deletes to cascade from one table in a relationship to another. For more information on referential constraints and the ON DELETE CASCADE option, refer to "Referential Integrity".
Deleting All Rows of a Table
The DELETE statement specifies a table and usually contains a WHERE clause that designates the row or rows that are to be removed from the table. If the WHERE clause is left out, all rows are deleted. Do not execute the following statement:
Because this DELETE statement does not contain a WHERE clause, all rows from the customer table are deleted. If you attempt an unconditional delete using the DB-Access or the SQL Editor menu options, the program warns you and asks for confirmation. However, an unconditional delete from within a program can occur without warning.
Deleting a Known Number of Rows
The WHERE clause in a DELETE statement has the same form as the WHERE clause in a SELECT statement. You can use it to designate exactly which row or rows should be deleted. You can delete a customer with a specific customer number, as the following example shows:
In this example, because the customer_num column has a unique constraint, you can ensure that no more than one row is deleted.
Deleting an Unknown Number of Rows
You can also choose rows that are based on nonindexed columns, as the following example shows:
Because the column that is tested does not have a unique constraint, this statement might delete more than one row. (Druid Cyclery might have two stores, both with the same name but different customer numbers.)
To find out how many rows a DELETE statement affects, select the count of qualifying rows from the customer table for Druid Cyclery.
You can also select the rows and display them to ensure that they are the ones you want to delete.
Using a SELECT statement as a test is only an approximation, however, when the database is available to multiple users concurrently. Between the time you execute the SELECT statement and the subsequent DELETE statement, other users could have modified the table and changed the result. In this example, another user might perform the following actions:
Although it is not likely that other users would do these things in that brief interval, the possibility does exist. This same problem affects the UPDATE statement. Ways of addressing this problem are discussed under "Concurrency and Locks", and in greater detail in Chapter 7, "Programming for a Multiuser Environment."
Another problem you might encounter is a hardware or software failure before the statement finishes. In this case, the database might have deleted no rows, some rows, or all specified rows. The state of the database is unknown, which is undesirable. To prevent this situation, use transaction logging, as discussed in "Interrupted Modifications".
Complicated Delete Conditions
The WHERE clause in a DELETE statement can be almost as complicated as the one in a SELECT statement. It can contain multiple conditions that are connected by AND and OR, and it might contain subqueries.
Suppose you discover that some rows of the stock table contain incorrect manufacturer codes. Rather than update them, you want to delete them so that they can be reentered. You know that these rows, unlike the correct ones, have no matching rows in the manufact table. The fact that these incorrect rows have no matching rows in the manufact table allows you to write a DELETE statement such as the one in the following example:
The subquery counts the number of rows of manufact that match; the count is 1 for a correct row of stock and 0 for an incorrect one. The latter rows are chosen for deletion.
One way to develop a DELETE statement with a complicated condition is to first develop a SELECT statement that returns precisely the rows to be deleted. Write it as SELECT *; when it returns the desired set of rows, change SELECT * to read DELETE and execute it once more.
The WHERE clause of a DELETE statement cannot use a subquery that tests the same table. That is, when you delete from stock, you cannot use a subquery in the WHERE clause that also selects from stock.
The key to this rule is in the FROM clause. If a table is named in the FROM clause of a DELETE statement, it cannot also appear in the FROM clause of a subquery of the DELETE statement.
Inserting Rows
The INSERT statement adds a new row, or rows, to a table. The statement has two basic functions. It can create a single new row using column values you supply, or it can create a group of new rows using data selected from other tables.
Single Rows
In its simplest form, the INSERT statement creates one new row from a list of column values and puts that row in the table. The following statement shows an example of adding a row to the stock table:
The stock table has the following columns:
The values that are listed in the VALUES clause in the preceding example have a one-to-one correspondence with the columns of this table. To write a VALUES clause, you must know the columns of the tables as well as their sequence from first to last.
Possible Column Values
The VALUES clause accepts only constant values, not expressions. You can supply the following values:
Some columns of a table might not allow null values. If you attempt to insert NULL in such a column, the statement is rejected. Or a column in the table might not permit duplicate values. If you specify a value that is a duplicate of one that is already in such a column, the statement is rejected. Some columns might even restrict the possible column values allowed. These restrictions are placed on columns using data integrity constraints. For more information on data restrictions, see "Database Privileges".
Only one column in a table can have the SERIAL data type. The database server generates values for a serial column. To make this happen when you insert values, specify the value zero for the serial column. The database server generates the next actual value in sequence. Serial columns do not allow null values.
You can specify a nonzero value for a serial column (as long as it does not duplicate any existing value in that column), and the database server uses the value. However, that nonzero value might set a new starting point for values that the database server generates. The next value the database server generates for you is one greater than the maximum value in the column.
Do not specify the currency symbols for columns that contain money values. Just specify the numeric value of the amount.
The database server can convert between numeric and character data types. You can give a string of numeric characters (for example, '-0075.6') as the value of a numeric column. The database server converts the numeric string to a number. An error occurs only if the string does not represent a number.
You can specify a number or a date as the value for a character column. The database server converts that value to a character string. For example, if you specify TODAY as the value for a character column, a character string that represents the current date is used. (The DBDATE environment variable specifies the format that is used.)
Listing Specific Column Names
You do not have to specify values for every column. Instead, you can list the column names after the table name and then supply values for only those columns that you named. The following example shows a statement that inserts a new row into the stock table:
Only the data for the stock number, description, unit price, and manufacturer code is provided. The database server supplies the following values for the remaining columns:
This means that you must list and supply values for all columns that do not specify a default value or do not permit nulls.
You can list the columns in any order, as long as the values for those columns are listed in the same order. For information about setting a default value for a column, see Chapter 9, "Implementing Your Data Model."
After the INSERT statement is executed, the following new row is inserted into the stock table:
Both unit and unit_descr are blank, indicating that null values are in those two columns. Because the unit column permits nulls, the number of tire pumps that were purchased for $114 is not known. Of course, if a default value of box were specified for this column, then box would be the unit of measure. In any case, when you insert values into specific columns of a table, pay attention to what data is needed for that row.
Multiple Rows and Expressions
The other major form of the INSERT statement replaces the VALUES clause with a SELECT statement. This feature allows you to insert the following data:
For example, suppose a follow-up call is required for every order that has been paid for but not shipped. The INSERT statement in the following example finds those orders and inserts a row in cust_calls for each order:
This SELECT statement returns two columns. The data from these columns (in each selected row) is inserted into the named columns of the cust_calls table. Then, an order number (from order_num, a serial column) is inserted into the call description, which is a character column. Remember that the database server allows you to insert integer values into a character column. It automatically converts the serial number to a character string of decimal digits.
Restrictions on the Insert-Selection
The following list contains the restrictions on the SELECT statement for inserting rows:
The INTO, INTO TEMP, and ORDER BY clause restrictions are minor. The INTO clause is not useful in this context. (It is discussed in Chapter 5, "Programming with SQL.") To work around the INTO TEMP clause restriction, first select the data you want to insert into a temporary table and then insert the data from the temporary table with the INSERT statement. Likewise, the lack of an ORDER BY clause is not important. If you need to ensure that the new rows are physically ordered in the table, you can first select them into a temporary table and order it, and then insert from the temporary table. You can also apply a physical order to the table using a clustered index after all insertions are done.
The last restriction is more serious because it prevents you from naming the same table in both the INTO clause of the INSERT statement and the FROM clause of the SELECT statement. Naming the same table in both the INTO clause of the INSERT statement and the FROM clause of the SELECT statement causes the database server to enter an endless loop in which each inserted row is reselected and reinserted.
In some cases, however, you might want to do this. For example, suppose that you have learned that the Nikolus company supplies the same products as the Anza company, but at half the price. You want to add rows to the stock table to reflect the difference between the two companies. Optimally, you want to select data from all the Anza stock rows and reinsert it with the Nikolus manufacturer code. However, you cannot select from the same table into which you are inserting.
To get around this restriction, select the data you want to insert into a temporary table. Then select from that temporary table in the INSERT statement as the following example shows:
This SELECT statement takes existing rows from stock and substitutes a literal value for the manufacturer code and a computed value for the unit price. These rows are then saved in a temporary table, anzrows, which is immediately inserted into the stock table.
When you insert multiple rows, a risk exists that one of the rows contains invalid data that might cause the database server to report an error. When such an error occurs, the statement terminates early. Even if no error occurs, a very small risk exists that a hardware or software failure might occur while the statement is executing (for example, the disk might fill up).
In either event, you cannot easily tell how many new rows were inserted. If you repeat the statement in its entirety, you might create duplicate rows, or you might not. Because the database is in an unknown state, you cannot know what to do. The answer lies in using transactions, as discussed in "Interrupted Modifications".
Updating Rows
You use the UPDATE statement to change the contents of one or more columns in one or more existing rows of a table. This statement takes two fundamentally different forms. One lets you assign specific values to columns by name; the other lets you assign a list of values (that might be returned by a SELECT statement) to a list of columns. In either case, if you are updating rows, and some of the columns have data integrity constraints, the data you change must be within the constraints placed on those columns. For more information, refer to "Database Privileges".
Selecting Rows to Update
Either form of the UPDATE statement can end with a WHERE clause that determines which rows are modified. If you omit the WHERE clause, all rows are modified. The WHERE clause can be quite complicated to select the precise set of rows that need changing. The only restriction on the WHERE clause is that the table that you are updating cannot be named in the FROM clause of a subquery.
The first form of an UPDATE statement uses a series of assignment clauses to specify new column values, as the following example shows:
The WHERE clause selects the row to be updated. In the stores7 database, the customer.customer_num column is the primary key for that table, so this statement can update no more than one row.
You can also use subqueries in the WHERE clause. Suppose that the Anza Corporation issues a safety recall of their tennis balls. As a result, any unshipped orders that include stock number 6 from manufacturer ANZ must be put on back order, as the following example shows:
This subquery returns a column of order numbers (zero or more). The UPDATE operation then tests each row of orders against the list and performs the update if that row matches.
Updating with Uniform Values
Each assignment after the keyword SET specifies a new value for a column. That value is applied uniformly to every row that you update. In the examples in the previous section, the new values were constants, but you can assign any expression, including one based on the column value itself. Suppose the manufacturer code HRO has raised all prices by 5 percent, and you must update the stock table to reflect this increase. Use a statement such as the following :
You can also use a subquery as part of the assigned value. When a subquery is used as an element of an expression, it must return exactly one value (one column and one row). Perhaps you decide that for any stock number, you must charge a higher price than any manufacturer of that product. You need to update the prices of all unshipped orders. The SELECT statements in the following example specify the criteria:
The first SELECT statement returns a single value: the highest price in the stock table for a particular product. The first SELECT statement is a correlated subquery because, when a value from items appears in the WHERE clause for the first SELECT statement, you must execute it for every row that you update.
The second SELECT statement produces a list of the order numbers of unshipped orders. It is an uncorrelated subquery that is executed once.
Impossible Updates
Restrictions exist on the use of subqueries when you modify data. In particular, you cannot query the table that is being modified. You can refer to the present value of a column in an expression, as in the example in which the unit_price column was incremented by 5 percent. You can refer to a value of a column in a WHERE clause in a subquery, as in the example that updated the stock table, in which the items table is updated and items.stock_num is used in a join expression.
The need to update and query a table at the same time does not occur often in a well-designed database. (Database design is covered in Chapter 8 and Chapter 9.) However, you might want to update and query at the same time when a database is first being developed, before its design has been carefully thought through. A typical problem arises when a table inadvertently and incorrectly contains a few rows with duplicate values in a column that should be unique. You might want to delete the duplicate rows or update only the duplicate rows. Either way, a test for duplicate rows inevitably requires a subquery, which is not allowed in an UPDATE statement or DELETE statement. Chapter 6, "Modifying Data Through SQL Programs," discusses how to use an update cursor to perform this kind of modification.
Updating with Selected Values
The second form of UPDATE statement replaces the list of assignments with a single bulk assignment, in which a list of columns is set equal to a list of values. When the values are simple constants, this form is nothing more than the form of the previous example with its parts rearranged, as the following example shows:
No advantage exists to writing the statement this way. In fact, it is harder to read because it is not obvious which values are assigned to which columns.
However, when the values to be assigned come from a single SELECT statement, this form makes sense. Suppose that changes of address are to be applied to several customers. Instead of updating the customer table each time a change is reported, the new addresses are collected in a single temporary table named newaddr. It contains columns for the customer number and the address-related fields of the customer table. Now the time comes to apply all the new addresses at once.
The values for multiple columns are produced by a single SELECT statement. If you rewrite this example in the other form, with an assignment for each updated column, you must write five SELECT statements, one for each column to be updated. Not only is such a statement harder to write but it also takes much longer to execute.
|
