Using DELETE

Just as data sometimes needs to be updated, sometimes it also needs to be removed from a database table. People get new jobs, products are discontinued, and so on. When the time comes to remove something from a table in our database, we use the DELETE command. The basic syntax is as follows:

DELETE FROM <table_name> [WHERE <where_conditions>];

As with UPDATE statements, the WHERE part of a DELETE statement is optional, but if we leave it off, the command will delete every row in the table, which is even more catastrophic than leaving off the WHERE part in an UPDATE statement, if such a thing is possible. Make it a habit to always include it.

As an example, let's delete the Spencer Kimball employee:

DELETE FROM employees
WHERE givenname="Spencer" AND surname="Kimball"; 

As with the UPDATE examples, the WHERE clause is looking up the rows to delete by givenname and surname. A more precise method is to first look up the record to discover its primary key, and then to...