websql

20 Updating and Deleting Data

Learning Objectives

:

Use the UPDATE statement to modify specific rows or all rows in a table.

Set a column's value to NULL to remove existing data.

Use the DELETE statement to remove specific rows or all rows from a table.

Describe best practices for using UPDATE and DELETE safely.

20.1 Updating Data

To update (modify) data in a table, you use the UPDATE statement. UPDATE can be used in two ways:

To update specific rows in a table
To update all rows in a table

You'll now take a look at each of these uses.

Don't Omit the WHERE Clause. Special care must be exercised when using UPDATE because it is all too easy to mistakenly update every row in your table. Please read this entire section on UPDATE before using this statement.

UPDATE and Security. Use of the UPDATE statement might require special security privileges in client/server DBMSs. Before you attempt to use UPDATE, make sure you have adequate security privileges to do so.

The UPDATE statement is very easy to use — some would say too easy. The basic format of an UPDATE statement is made up of three parts:

The table to be updated
The column names and their new values
The filter condition that determines which rows should be updated

Let's take a look at a simple example. Recall the artist Elena Voss (artist_id 501) that you inserted in the previous lesson — her middle name was left as NULL because it wasn't on file at the time. It has since been discovered to be "Marie," so that record needs updating. The following statement performs this update:

UPDATE artist
SET middle_names = 'Marie'
WHERE artist_id = 501;

The UPDATE statement always begins with the name of the table being updated. In this example, it is the artist table. The SET command is then used to assign the new value to a column. As used here, the SET clause sets the middle_names column to the specified value:

SET middle_names = 'Marie'

The UPDATE statement finishes with a WHERE clause that tells the DBMS which row to update. Without a WHERE clause, the DBMS would update all the rows in the artist table with this new middle name — definitely not the desired outcome.

Updating multiple columns requires a slightly different syntax:

UPDATE artist
SET style = 'Abstract Expressionist', birth = 1961
WHERE artist_id = 501;

When you are updating multiple columns, you use only a single SET command, and each column = value pair is separated by a comma. (No comma is specified after the last column.) In this example, further research into Elena Voss's career refined her style classification and corrected her birth year, so columns style and birth will both be updated for artist 501.

Using Subqueries in an UPDATE Statement. Subqueries may be used in UPDATE statements, enabling you to update columns with data retrieved with a SELECT statement. Refer to the lesson on working with subqueries for more information on subqueries and their uses.

The FROM Keyword. Some SQL implementations support a FROM clause in the UPDATE statement that can be used to update the rows in one table with data from another table. Refer to your DBMS documentation to see if it supports this feature.

To delete a column's value, you can set it to NULL (assuming the table is defined to allow NULL values). Suppose the style reclassification for Elena Voss turns out to be inconclusive, and you'd rather leave the style unrecorded than guess. You can do this as follows:

UPDATE artist
SET style = NULL
WHERE artist_id = 501;

Here the NULL keyword is used to save no value to the style column. That is very different from saving an empty string. An empty string (specified as '') is a value, whereas NULL means that there is no value at all.

20.2 Deleting Data

To delete (remove) data from a table, you use the DELETE statement. DELETE can be used in two ways:

To delete specific rows from a table
To delete all rows from a table

Now let's take a look at each of these.

Don't Omit the WHERE Clause. Special care must be exercised when using DELETE because it is all too easy to mistakenly delete every row from your table. Please read this entire section on DELETE before using this statement.

DELETE and Security. Use of the DELETE statement might require special security privileges in client/server DBMSs. Before you attempt to use DELETE, make sure you have adequate security privileges to do so.

UPDATE is very easy to use, as you just saw. The good (and bad) news is that DELETE is even easier to use.

The following statement deletes a single row from the artist table (the row for Elena Voss, which you added in the previous lesson):

DELETE FROM artist
WHERE artist_id = 501;

This statement should be self-explanatory. DELETE FROM requires that you specify the name of the table from which the data is to be deleted. The WHERE clause filters which rows are to be deleted. In this example, only artist 501 will be deleted. If the WHERE clause were omitted, this statement would have deleted every artist in the table!

Foreign Keys Are Your Friend. Joins were introduced in the lesson on joining tables, and as you learned then, to join two tables, you simply need common fields in both of those tables. But you can also have the DBMS enforce the relationship by using foreign keys. (These can be defined when you create your tables, as described in the lesson on designing database tables.) When foreign keys are present, the DBMS uses them to enforce referential integrity. For example, if you tried to insert a new work into the work table, the DBMS would not allow you to insert it with an unknown artist ID because the artist_id column is connected to the artist table as a foreign key. So what does this have to do with DELETE? Well, a nice side effect of using foreign keys to ensure referential integrity is that the DBMS usually prevents the deletion of rows that are needed for a relationship. For example, if you tried to delete an artist from artist who still had works recorded in work, that DELETE statement would throw an error and would be aborted. That's another reason to always define your foreign keys.

The FROM Keyword. In some SQL implementations, the FROM keyword following DELETE is optional. However, it is good practice to always provide this keyword, even if it is not needed. Doing this will ensure that your SQL code is portable between DBMSs.

DELETE takes no column names or wildcard characters. DELETE deletes entire rows, not columns. To delete specific columns, you use an UPDATE statement.

Table Contents, Not Tables. The DELETE statement deletes rows from tables, even all rows from tables. But DELETE never deletes the table itself.

Faster Deletes. If you really do want to delete all rows from a table, don't use DELETE. Instead, use the TRUNCATE TABLE statement, which accomplishes the same thing but does it much quicker (because data changes are not logged).

20.3 Guidelines for Updating and Deleting Data

The UPDATE and DELETE statements used in the previous section all have WHERE clauses, and there is a very good reason for this. If you omit the WHERE clause, the UPDATE or DELETE will be applied to every row in the table. In other words, if you execute an UPDATE without a WHERE clause, every row in the table will be updated with the new values. Similarly, if you execute DELETE without a WHERE clause, all the contents of the table will be deleted.

Here are some important guidelines that many SQL programmers follow:

Never execute an UPDATE or a DELETE without a WHERE clause unless you really do intend to update and delete every row.
Make sure every table has a primary key (refer to the lesson on designing database tables if you have forgotten what this is), and use it as the WHERE clause whenever possible. (You may specify individual primary keys, multiple values, or value ranges.)
Before you use a WHERE clause with an UPDATE or a DELETE, first test it with a SELECT to make sure it is filtering the right records; it is far too easy to write incorrect WHERE clauses.
Use database-enforced referential integrity (refer to the lesson on joining tables for this one too) so that the DBMS will not allow the deletion of rows that have data in other tables related to them.
Some DBMSs allow database administrators to impose restrictions that prevent the execution of UPDATE or DELETE without a WHERE clause. If your DBMS supports this feature, consider using it.

The bottom line is that SQL has no Undo button. Be very careful using UPDATE and DELETE, or you'll find yourself updating and deleting the wrong data.

20.4 Summary

In this lesson, you learned how to use the UPDATE and DELETE statements to manipulate the data in your tables. You learned the syntax for each of these statements, as well as the inherent dangers they expose. You also learned why WHERE clauses are so important in UPDATE and DELETE statements, and you were given guidelines that should be followed to help ensure that data does not get damaged inadvertently.