The Delete Statement

DELETE FROM table-name [WHERE predicates]

You can use the DELETE SQL statement to delete one or more rows from a relation. When used without the WHERE clause, all rows in a table will be deleted. The WHERE clause is used in conjunction with selection predicates to limit the rows that are deleted. The WHERE predicates supported match those detailed in the SELECT section in previous pages.

The statement:

DELETE FROM Sales will result in all rows in the relation Sales being selected for deletion. Be cautious when issuing such a blanket command. More common will be a DELETE statement that utilizes a WHERE clause to control the deletion selection. The following example deletes rows from the Sales relation based upon the value of the SaleDate column.

DELETE FROM Sales

Note When deleting records from a relation, it is important to know the deletion scheme used by the database driver that you are using. Some will not remove and clear the space in your data table, while others will. This process could end up leaving holes of unused space in the table, slowing your program down. You may want to follow a DELETE with a process that "packs" the table.

Chapter 3—Structured Query Language ■ 65

The INSERT Statement

INSERT INTO tab le-name [ (column list) ] VALUES ( update values )

You can use the SQL INSERT statement to add new rows of data to a relation. The syntax for this DML statement changes a bit. The keyword FROM is replaced by INTO before declaring the target relation name. You may include a comma-separated list of column names that is surrounded by parentheses to the statement to explicitly define the data targets. If you do not include the column list, as in the following statement:

INSERT INTO Sales

the data contained in the VALUES clause is inserted into the columns of the relation on a positional basis, as the columns appear in the relation definition. Without the column list, you must supply the identical number of values as columns in the relation definition.

Specifying a column list gives your SQL statement much more control over the insertion process. When a column list is included, the VALUES clause now corresponds one to one with the column list, inserting the values into the same enumerated slot.

INSERT INTO SALES ( ITEM-NUMBER, ITEM-DESCRIPTION) VALUES ('833','Lobster')

In this statement, "833" will be entered for the Item-Number value and "Lobstef' for the Item-Description. Any columns not included in the column list will have a NIL inserted.

Was this article helpful?

0 0

Post a comment