Home > Articles

A Crash Course

  • Print
  • + Share This
This chapter is from the book

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.

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'

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

Note

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
  • + Share This
  • 🔖 Save To Your Account