Of course, the purpose of a database is to manage data. Sometimes, you want to edit data after it’s already in the table. SQL includes handy commands for this task: UPDATE and DELETE. The UPDATE command modifies the value of an existing record, and the DELETE command removes a record altogether.
How to update a record
Say that you decide to modify Bill Gates’s address to reinforce a recent marketing triumph. The following SQL code does the trick:
UPDATE contact SET email = '[email protected]' WHERE name = 'Bill Gates';
The UPDATE command has a few parts:
The UPDATE command. This indicates which table you will modify.
The SET command. This indicates a new assignment.
Assign a new value to a field. This uses a standard programming-style assignment statement to attach a new value to the indicated field. You can modify more than one field at a time. Just separate the field = value pairs with commas.
Specify a WHERE clause. You don’t want this change to happen to all the records in your database. You want to change only the e-mail address in records where the name is Bill Gates. Use the WHERE clause to specify which records you intend to update.
More than one person in your database may be named Bill Gates. Names aren’t guaranteed to be unique, so they aren’t really the best search criteria. This situation is actually a very good reason to use primary keys. A better version of this update looks as follows:
UPDATE contact SET email = '[email protected]' WHERE contactID = 1;
The contactID is guaranteed to be unique and present, so it makes an ideal search criterion. Whenever possible, UPDATE (and DROP) commands should use primary key searches so that you don’t accidentally change or delete the wrong record.
How to delete a record
Sometimes, you need to delete records. SQL has a command for this eventuality, and it’s pretty easy to use:
WHERE contactID = 1;
The preceding line deletes the entire record with a contactID of 1.
Be very careful with the DELETE command — it’s destructive. Be absolutely sure that you have a WHERE clause, or you may delete all the records in your table with one quick command! Likewise, be sure that you understand the WHERE clause so that you aren’t surprised by what gets deleted.
You’re better off running an ordinary SELECT using the WHERE clause before you DELETE, just to be sure that you know exactly what you’re deleting. Generally, you should DELETE based on only a primary key so that you don’t produce any collateral damage.
