Databases and SQL - Changing Information In A Database
(Page 4 of 5 )
Occasionally data that is stored in a database may become stale. SQL's DELETE, UPDATE and DROP commands allow you to manage records and keep the information current.
Delete
The DELETE command is used to remove records from a table. Issue the DELETE command followed by FROM, the table that holds the records, WHERE and a condition statement that will be used to match the records.
DELETE from addresses where first_name = "Harold" AND last_name = "Smith";
The example query would remove Harold Smith's record from the addresses table.
Update
The UPDATE command can be used to update a record's fields. Issue the UPDATE command followed by the table name, SET, the field names and their new values, WHERE and a condition statement that will be used to match the correct record.
For example, assume Gwendolyn Adams from our previous examples has decided to move to Milwaukee, WI. Her record in the database needs to be updated to reflect her new address.
UPDATE addresses SET address = "613 Wright St.", city = "Milwaukee", state = "WI", zip_code="53216" WHERE (first_name = "Gwendolyn" AND last_name = "Adams");
The query would update the record in the addresses table for Ms. Adams to store her new address of 613 Write St. Milwaukee, WI, 53216.
Drop
When data is no longer needed, the DROP command can be used to delete a table or an entire database. To delete a table, issue the DROP command, followed by TABLE and the name of the table to be dropped.
DROP TABLE addresses;
To delete an entire database, issue the DROP command, followed by DATABASE and the name of the database to be dropped.
DROP DATABASE myDatabase;
Be careful! Deleting entire tables and databases cannot be undone so one should be extremely cautious when using the DROP command.
