Deleting Data


Deleting data from a table is even easier than adding or updating dataperhaps too easy.

You use the SQL DELETE statement to delete data. The statement takes only two parametersone required and one optional:

  • The name of the table from which to delete the data must be specified immediately following the words DELETE FROM.

  • An optional WHERE clause can be used to restrict the scope of the deletion process.

The DELETE statement is dangerously easy to use. Look at the following line of code (but don't execute it):

 DELETE FROM Directors 

This statement removes all directors from the Directors table, and does it without any warnings or confirmation.

TIP

Some databases, in particular client/server databases (such as Microsoft SQL Server and Oracle), offer safeguards against accidental or malicious deletions. There generally are two approaches to preventing mass deletion.

One is to create a trigger (a piece of code that runs on the server when specific operations occur) that verifies every DELETE statement and blocks any DELETE without a WHERE clause.

A second is to restrict the use of DELETE without a WHERE clause based on login name. Only certain users, usually those with administrative rights, are granted permission to execute DELETE without a WHERE clause. Any other user attempting a mass DELETE will receive an error message, and the operation will abort.

Not all database systems support these techniques. Consult the database administrator's manuals to ascertain which safeguards are available to you.


The DELETE statement is most often used with a WHERE clause. For example, the following SQL statement deletes a single director (the one you just added) from the Directors table:

 DELETE FROM Directors WHERE DirectorID=14 

To verify that the row was deleted, retrieve all the Directors one last time (as seen in Figure 7.6).

Figure 7.6. Most databases delete rows immediately, as opposed to flagging them for deletion. This this will be reflected when listing the table contents.


As with all WHERE clauses, the DELETE statement's WHERE clause can be a SELECT statement that retrieves the list of rows to delete. If you do use a SELECT statement for a WHERE clause, be careful to test the SELECT statement first to ensure that it retrieves all the values you want, and only those values.

TIP

Feel free to INSERT, UPDATE, and DELETE rows as necessary, but when you're finished either clean up the changes or just copy overwrite the data file with the original (to restore it to its original state).


NOTE

Primary key values are never reused. If you INSERT rows after you have performed delete operations, the new rows will be assigned brand-new IDs, and the old (deleted) IDs will not be reused. This behavior is a required part of how relational databases work, and was explained in Chapter 5, "Building the Databases."




Macromedia Coldfusion MX 7 Web Application Construction Kit
Macromedia Coldfusion MX 7 Web Application Construction Kit
ISBN: 321223675
EAN: N/A
Year: 2006
Pages: 282

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net