Modifying Data with UPDATE and DELETE

for RuBoard

Modifying Data with UPDATE and DELETE

So far, you have seen how to retrieve and add data to the database. However, suppose you would like to modify existing database rows. To modify data, you would use the UPDATE SQL statement. A simplified version of the syntax of the UPDATE statement looks like the code in Listing 3.3.

Listing 3.3 The Syntax of the UPDATE SQL Statement

 UPDATE  table_name  SET  column_name  =  expression  WHERE  search_conditions  

The specific example in Listing 3.4 explains the syntax quite well. After the statement in Listing 3.4 is executed against the data source, any employee with last name of "Peacock" and first name of "Margaret" as specified by the WHERE clause will be changed to "Hogue" as specified by the SET clause of the statement. Figure 3.3 shows the change.

Figure 3.3. The value in the LastName column changes for the selected employee.

Listing 3.4 Using the SQL UPDATE Statement to Change an Employee's Last Name

 UPDATE      employees SET      LastName = 'Hogue' WHERE      LastName = 'Peacock' and      FirstName = 'Margaret' 

Be careful when using the UPDATE statement, particularly when working with live data. Remember that every row meeting the conditions of the WHERE clause in the statement will be updated. In fact, if you inadvertently do not include the WHERE clause in the statement, your query will affect every single row in the table!

It's also possible to update several fields at once. You only need to place commas between each segment as in Listing 3.5.

Listing 3.5 Updating Multiple Columns in a Single UPDATE Statement

 UPDATE      employees SET      LastName = 'Hogue',      Address = '11 Longfellow St.' WHERE      LastName = 'Peacock' and      FirstName = 'Margaret' 

Compared to updating database rows, deleting database rows is easy. Listing 3.6 shows the syntax of the DELETE SQL statement. It is the simplest query you have seen thus far. All you need to specify is the name of the table and the search conditions.

Listing 3.6 Deleting Rows from the Employee Table

 DELETE FROM  table_name  WHERE  search_conditions  

To delete the employee with EmployeeID of 7, you use the query in Listing 3.7. Remember that if you are deleting only a single row, your search conditions must single out that row. Normally, the purpose of an ID field in a database table is to guarantee this uniqueness.

Listing 3.7 Deleting Rows from the Employee Table

 DELETE FROM      employees WHERE      EmployeeID = 7 

for RuBoard