- Hour 3: Using T-SQL: A Crash Course
- Adding New Data with INSERT
- Modifying Data with UPDATE and DELETE
- Using the Built-in SQL Functions
- Summary
- Q&A
- Workshop
This chapter is from the book
This chapter is from the book
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.
){kind=link}
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