Modifying Data

You use the SQL UPDATE statement to update one or more columns. This usually involves specifying the following:

• The table containing the data you want to update.

• The column or columns you want to update, preceded by the SET keyword. If you specify more than one item, each must be separated by a comma.

• An optional WHERE clause to specify which rows to update. If no WHERE clause is provided, all rows are updated.

Try updating a row. Enter the following SQL statement (ensuring that the ID number used in the WHERE clause is the DirectorID you noted earlier).

 UPDATE Directors SET FirstName='Ben' WHERE DirectorID = 14 

Your code should look like the example in Figure 7.4 (although the DirectorID might be different). Click Execute to perform the update. Again, no results will be displayed, as UPDATE doesn't return data.

Figure 7.4. Update statements can be entered manually, and entered on one line or broken over many lines.

If you now select the contents of the Directors table, you see that the new director's first name has been changed.

Understanding UPDATE

Now, take a closer look at the SQL statement you just used. The first line issued the UPDATE statement and specified the name of the table to update. As with the INSERT and DELETE statements, the table name is required.

You next specified the column you wanted to change and its new value:

 SET FirstName='Ben' 

This is an instruction to update the FirstName column with the text Ben. The SET keyword is required for an UPDATE operation, because updating rows without specifying what to update makes little sense.

The SET keyword can be used only once in an UPDATE statement. If you are updating multiple rowsfor example, to change Benjamin to Ben and to set the LastName to Forta in one operationthe SET keyword would look like this:

 SET FirstName='Ben', LastName='Forta' 

When updating multiple columns, each column must be separated by a comma. The complete (revised) UPDATE statement would then look like this:

 UPDATE Directors SET FirstName='Ben', LastName='Forta' WHERE DirectorID = 14 

The last line of the code listing specifies a WHERE clause. The WHERE clause is optional in an UPDATE statement. Without it, all rows will be updated. The following code uses the primary key column to ensure that only a single row gets updated:

 WHERE DirectorID = 14 

To verify that the updates worked, try retrieving all the data from the Directors table. The results should be similar to those seen in Figure 7.5 (showing the updated final row).

Figure 7.5. When experimenting with updates, it's a good idea to retrieve the table contents to check that the update worked properly.

CAUTION

Be sure to provide a WHERE clause when using the SQL UPDATE statement; otherwise, all rows will be updated.

Making Global Updates

Occasionally, you will want to update all rows in a table. To do this, you use UPDATE, tooyou just omit the WHERE clause, or specify a WHERE clause that matches multiple rows.

When updating multiple rows using a WHERE clause, always be sure to test that WHERE clause with a simple SELECT statement before executing the UPDATE. If the SELECT returns the correct data (i.e., the data you want updated), you'll know that it is safe to use with UPDATE. If you don't, you might update the wrong data!

TIP

Before executing INSERT, UPDATE, or DELETE operations that contain complex statements or WHERE conditions, you should test the statement or condition by using it in a SELECT statement. If SELECT returns incorrect statement results or an incorrect subset of data filtered by the WHERE clause, you'll know that the statement or condition is incorrect. Unlike INSERT, UPDATE, and DELETE, the SELECT statement never changes any data,. So if an error exists in the statement or condition, you'll find out about it before any damage is done.