Another step you can easily take on existing data is to entirely remove it from the database. To do this, you use the DELETE command.
DELETE FROM tablename WHERE column=value
Note that once you have deleted a record, there is no way of retrieving it, so you may want to back up your database before performing any deletes (see Chapter 13, "MySQL Administration"). Also, you should get in the habit of using WHERE when deleting data, or else you will delete all of the data in a table. The query DELETE FROM tablename will empty out a table, while still retaining its structure. Similarly, the command TRUNCATE TABLE tablename will delete an entire table (both the records and the structure) and recreate the structure. The end result is the same, but this method is faster and safer.
Another issue with deleting records has to do with the integrity of a relational database. In the accounting example, since the invoices table has a client_id field, if a client is deleted you might create phantom records because certain invoices, which kept that client_id reference, are now linked to a nonexistent client. Until MySQL formally supports foreign key relationships, this will be an issue to watch out for. In short, do not delete any records without altering the corresponding related records.
I'll go through an example of this by explaining the steps I would take if I decided to combine all of the Travel expense categories into one.
To delete data:
View the current expense categories (Figure 5.26
SELECT * FROM expense_categories ORDER BY expense_category ASC;
To determine which fields I am combining, I'll look at the contents of the table one last time. I should also make note of what expense_category_id
s are represented by travel categories, which are 1 and 2.
Figure 5.26. To delete all of the travel expense categories, I'll list them in alphabetical order first.
Delete the two records from the table (Figure 5.27
DELETE FROM expense_categories WHERE expense_category_id IN (1, 2);
To be sure that I am deleting the right rows, I make use of the primary keys. I could have also used a query like DELETE FROM expense_categories WHERE expense_category LIKE 'Travel-%'
, although that would have been less precise.
Figure 5.27. Two records are deleted using the primary key to refer to them.
Create a new Travel
INSERT INTO expense_categories VALUES (NULL, 'Travel');
Retrieve the Travel category's expense_category_id
SELECT expense_category_id FROM
You'll notice that deleting records from tables leaves gaps in your primary keys (in terms of auto-incrementation). Although expense_categories
is currently missing numbers 1
, the next record is inserted at 24
. This is perfectly fine, as the primary key value is arbitrary.
Figure 5.28. The next step in the process is to insert and select a new category.
Update the expenses
table to reflect these changes (Figure 5.29
UPDATE expenses SET expense_category_id = 24
WHERE expense_category_id IN (1, 2);
Because the expense_categories
table relates to the expenses
table, I must apply the changes made to the one to the other.
Figure 5.29. Finally, I should update the related expenses table to incorporate the new changes.
To delete all of the data in a table, as well as the table itself, use DROP.
To delete an entire database, including every table therein and all of its data, use
DROP DATABASE databasename
Remember that if you log in to the mysql client with the --i-am-a-dummy parameter, mysql will not allow you to run UPDATE or DELETE queries without a WHERE conditional.
Beginning with MySQL version 4.0, you can run a DELETE query across multiple tables at the same time.