At one point or another, we will want to delete some data that we had previously inserted into the database. To do this we must use a MySQL DELETE query. To perform a delete, we only need to specify the criteria that we will require in order to confirm that the delete should happen.
Care must be taken when using the delete query as there is usually no way to get back data once it has been deleted.
In the tutorial below, we will examine how to properly and safely use the MySQL delete query.
The syntax for this dangerous command is shown below:
DELETE FROM table_name WHERE condition;
The condition that we specify for our DELETE query may be an exact match on some data in a column or it may be a match made using wildcards or some other criteria. This target data will almost certainly be narrowed down using the MySQL where clause.
Once again, let us assume that we have our trusty "employees" table in our database:
| first_name | last_name |
|---|---|
| Paul | Pitterson |
| Francine | Beecham |
| Raul | DiNozzo |
| Anthony | DiNozzo |
Suppose Raul DiNozzo got fired, we would remove him using the following command:
DELETE FROM employees WHERE first_name = 'Raul';
After that query was run, our database would look like:
| first_name | last_name |
|---|---|
| Paul | Pitterson |
| Francine | Beecham |
| Anthony | DiNozzo |
Note that we have to be extra careful when specifying the WHERE clause of an update or delete. Keep in mind that one or more records may have similar data. In this example, one or more persons could have similar first names or last names so we should not depend on the uniqueness of a name to determine if a delete or update should happen.
These are only simple examples to get you started and we will see more robust examples which prevent accidental modifications. Ideally, we would want to use something like a unique employee number for each employee, and then perform a delete based on the employee number. As we said before though, these are just simple examples to get you started.
For non-prodction or testing databases, we might get away with being careless, but it is always a good idea to write SQL with proper WHERE clauses and back up the database before running queries that will modify it.