UPDATE that Data!

If you've read previous installments of this column, you've learned the basics of SQL for finding data as well as how to insert it into a database. But how do you manipulate it once it's in there? This installment focuses on using the aptly named UPDATE SQL statement to change existing database records.

The UPDATE statement

Just as with the previous SELECT and INSERT SQL statements, the UPDATE statement is in the rough format of an English sentence:

UPDATE table_name SET column_name = value

So, to go back to the MusicCollection table we've been using throughout the column, I think we need to change the Artist field of the third album from Bruce Hornsby to "Bruce Hornsby and the Range" to be more accurate. The current table looks something like this:

We can try to use the UPDATE statement to make our change (remembering that text values are always enclosed in single quotes in SQL).

UPDATE MusicCollection SET Artist ='Bruce Hornsby and the Range';

and the result is

Not what I had hoped for at all! SQL engines are extremely literal -- all the values in the column Artist were UPDATEd to "Bruce Hornsby and the Range," which is certainly not the behavior we were after.

This illustrates a fundamental difference between the INSERT and UPDATE statements -- UPDATE affects all rows in the database, while INSERT can only insert a single record (though we'll see ways around that in a future column). This behavior can be useful in some situations, for example when a country changes its name or a city adds a new ZIP code or area code for its citizens, but in general is not the desired result.

So how do you update a single record? If you've read the articles on SELECT statements in the past few weeks, you know that SQL has a WHERE clause which can be used to filter the results of a query. We can leverage the WHERE clause here as well to filter the data before we apply the UPDATE.

UPDATE in action

The WHERE clause is used to modify a SQL statement by limiting the operations to a filtered subset of the database. But what criteria do you use to filter? We can fix our previous example using the following statement:

UPDATE MusicCollection SET Artist ='Bruce Hornsby and the Range' WHERE ID=3;

This is one possible formulation. But there are many more:

• UPDATE MusicCollection SET Artist ='Bruce Hornsby and the Range' WHERE Artist='Bruce Hornsby';
• UPDATE MusicCollection SET Artist ='Bruce Hornsby and the Range' WHERE Artist='Bruce Hornsby' AND ID=3;
• UPDATE MusicCollection SET Artist ='Bruce Hornsby and the Range' WHERE Artist='Bruce Hornsby';

or any other combination of filtering criteria that result in the appropriate data being selected for the update. As I've mentioned before,

SQL Wisdom #1) There are often multiple ways to implement a SQL query to produce a given result.

If you're new to SQL, you should realize that it can be extremely difficult to undo an UPDATE if you're not careful. At best, it's tedious and error-prone with moments of terror and panic.

How do you avoid those feelings? I'd suggest using a SELECT * statement with the same WHERE clause, which gives you a quick view of the records that will be affected before you do anything rash. Some database administrators go as far as to password-restrict this SQL statement to only certain users to prevent damage to the database.

You've seen me update a single field, but how can you update an entire record like a contact record in a personal information manager? You can do it with a single UPDATE statement with multiple column_name/value pairs separated by commas:

UPDATE PIM SET
Address='100 2nd St SW',
ZIP='22222',
HomePhone='800-555-1212'
WHERE UserID=22;

This SQL statement updates the Address, ZIP, and HomePhone fields of the record(s) where UserID is equal to 22. It is extremely important to realize that only the named fields are changed -- everything else retains its original value.

Next steps

This week we added the SQL UPDATE statement to our bag of tricks. The syntax is

UPDATE table_name SET column_name1 = value1 [, column_name2 = value2, etc.] WHERE criteria

with the WHERE not strictly required, but almost always necessary to prevent mass updates of entire columns.

The next installment will focus on the final fundamental data manipulation command where we finally learn how to remove data from the database. Until then, keep experimenting with SELECT, INSERT, UPDATE, and the WHERE clause as you continue to learn aboutSQL.

John Paul Ashenfelter is president and CTO of TransitionPoint.com, a technology development and analysis group focusing on web database applications.

Read more aboutSQL columns.

Discuss this article in the O'Reilly Network Linux Forum.

Return to the Linux DevCenter.

Copyright © 2007 O'Reilly Media, Inc.