MySQL I : Update and Delete

1. The structure of and the information in tables changes, you need to be able to update them.
2. Select a record:
SELECT client_street, client_city, client_state, client_zip FROM clients
WHERE client_id = 3;
3. Perhaps the address has changed. To update this field:
UPDATE clients SET client_street = ‘1313 Blackbird Lane’, client_city=’Castle Rock’, client_state=’ME’, client_zip = ‘13131’ where client_id = 3;
4. Check your results:
SELECT client_street, client_city, client_state, client_zip FROM clients
WHERE client_id = 3;
5. To delete records, like those associated with travel expenses, we may need to do some preparation:
SELECT * FROM expense_categories
ORDER BY expense_category ASC;

Make a note of what expense_category_id ‘s are represented by travel categories: 1, 2, 18
6. Delete these three records. Type:
DELETE FROM expense_categories
WHERE
expense_category_id = 1 OR
expense_category_id = 2 OR
expense_category_id = 18;
7. Check your results:
SELECT * FROM expense_categories
ORDER BY expense_category ASC;
8. Create a new travel category:
INSERT INTO expense_categories VALUES (NULL, ‘Travel’);
9. Check your results:
SELECT * FROM expense_categories
ORDER BY expense_category ASC;

Note that AUTO_INCREMENT primary key values are not replaced. Every number, including deleted ones, should remain unique.
10. Update the “expenses” table to reflect these changes:
UPDATE expenses SET
expense_category_id = 21 WHERE
expense_category_id = 1 OR
expense_category_id = 2 OR
expense_category_id = 18;
11. Check your results:
SELECT * FROM expenses;
12. This is tricky, and it’s easy to forget what is associated. Watch out or you will loose referential integrity. This will be a great reason to upgrade to version 4 of MySQL when it stabilized—cascading deletes!
13. To ALTER a table column structure
ALTER TABLE clients
CHANGE COLUMN contact_name contact_first_name VARCHAR(15);

This changed the contact_name field which was originally VARCHAR(40)

Check by typing:
SELECT contact_name FROM clients; an error is returned.
SELECT contact_first_name FROM clients; names are returned, but still lumped together.
14. Create a new contact_last_name column:
ALTER TABLE clients ADD COLUMN contact_last_name VARCHAR(25);
15. View and update the contact name information:
SELECT client_id, contact_first_name, contact_last_name FROM clients
WHERE contact_first_name IS NOT NULL;

UPDATE clients SET contact_first_name = ‘Jane’, contact_last_name = ‘Doe’
WHERE client_id = 1;

Then check your update:
SELECT client_id, contact_first_name, contact_last_name FROM clients
WHERE contact_first_name IS NOT NULL;

Note: You would need to do this eight more times to update all the name information. Script this!

Note: When adding a new column to a table you can use the AFTER directive.
Example (don’t type this):
ALTER TABLE clients
ADD COLUMN contact_last_name VARCHAR(25) AFTER contact_first_name;

Note: Changing column order is not allowed. You need to dump table contents, drop the table, recreate with the new column order, edit the data to reflect this change, and re-import.