Deleting Data
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:
1. | 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_ids are represented by travel categories, which are 1 and 2.
| 2. | 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.
| 3. | Create a new Travel category.
INSERT INTO expense_categories VALUES (NULL, 'Travel'); | 4. | Retrieve the Travel category's expense_category_id ( Figure 5.28).
SELECT expense_category_id FROM
expense_categories WHERE
expense_category= 'Travel';
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 and 2, the next record is inserted at 24. This is perfectly fine, as the primary key value is arbitrary.
| | | 5. | 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.
|
 Tips
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.
|
Js editor
Website development