This is the part 3 and final part of these three series of basic MySQL commands; even though, there are many other commands and this is a topic that could be covered more in depth, I decided to kind of compile of what I think are the more fundamentals and used command for database manipulation in MySQL, in this part will cover:
- How to use the UPDATE and REPLACE commands to modify existing records
- How to use the DELETE command to remove records
Using UPDATE Command to Modify Records:
UPDATE is the SQL command used to modify the contents of one or more columns in an existing record. The most basic UPDATE syntax looks like this:
SET column1=’new value’,
column2 =’new value2′
The guidelines for updating a record are similar to those used when inserting a record, the data you’re entering must be appropriate to the data type of the field, and you must enclose your strings in a single or double cuotes, escaping where necessary.
For example, assume you have a table called fruit containing an ID, a fruit name, and the status of the fruit.
To update the status of the fruit to “ripe”, use
UPDATE fruit SET stats = ‘ripe’;
You must be very careful and use condition when updating a table, unless you really intend to change all the columns for all records to the same value. For the sake of argument, assume that “grape” is spelled incorrectly in the table, and you want to use UPDATE to correct this mistake. This query would have horrible results:
UPDATE fruit SET fruit_name = ‘grape’;
now your fruit table would look like this:
All your fruit records are now grapes. Through attempting to correct the spelling of the field, all fields were changed because no condition was specified! When giving UPDATE privileges to your users, think twice about it, because one wrong UPDATE and your entire table could be showing only “grapes”.
Making a conditional UPDATE means that you are using WHERE clauses to match specific records. Using a WHERE clause in an UPDATE statement is just like using a WHERE clause in a SELECT statement. All the same comparison and logical operators can be used, such as “equal to”, “greater than”, “OR”, “AND”, etc…
Assume your fruit table has not been completely filled with grapes, but instead contains statement to fix the spelling mistake (“grappe” instead of “grape”). The UPDATE statement to fix the spelling mistake would be:
UPDATE fruit SET fruit_name = ‘grape’ WHERE fruit_name = ‘grappe’;
In this case, only one row was matched and one row was changed. Your fruit table should be intact, and all fruit names should be spelled properly.
Using Existing Column Values With UPDATE:
Another feature of UPDATE is the capability to use the current value in the record as the base value. For example, go back to the grocery_inventory table used in part II of this tutorials.
SELECT * FROM grocery_inventory;
When someone purchases an apple, the inventory table should be updated accordingly however, you won’t know exactly what number to enter in the curr_qty column, just that you sold one. In this case, use the current value of the column and subtract one:
UPDATE grocery_inventory SET curr_qty = curr_qty – 1 WHERE id = 1;
This should give you a new value of 999 in the curr_qty column, and indeed it does:
Using the REPLACE Command:
Another method for modifying records is to use the REPLACE commands, which is remarkably similar to the INSERT command.
REPLACE INTO table_name (column list) VALUES (column values);
The REPLACE command works like this: if the record you are inserting into the table contains a primary key value that matches a record already int the table, the record in the table will be deleted and the new record inserted in its place. Using the grocery_inventory table the following command will replace the entry for “apples”.
REPLACE INTO grocery_inventory VALUES (1, ‘pears’, ‘bunches of pears’, 0.5, 100);
If you use a REPLACE statement, and the value of the primary key in the new record does not match a value for a primary key already in the table, the record would simply be inserted and only one row would be affected.
Using the DELETE Command:
The basic DELETE syntax is:
DELETE FROM table_name
[WHERE some condition_is_true]
Notice there is no column specification in the delete command, when you DELETE entire record is removed. You might remember earlier the dangers of during the UPDATE command in the “fruit” table without specifying a condition and caused all records to be updated, You must be similarly careful when using DELETE.
This statement will remove all records in a table, keep in mind that this does not delete the table.
DELETE FROM fruit;
you can always verify the deletion by attempting to SELECT data from the table.
SELECT * FROM fruit;
by now all your records should be gone.
A conditional DELETE statement, just like a conditional SELECT or UPDATE statement means you are using WHERE clauses to match specif records. You have the full range of comparison and logical operators available to you, so you can pick and choose which records you want to delete. In this example we remove all records for “rotten” fruit from the fruit table
DELETE FROM fruit WHERE stats = ‘rotten’;
Three records were deleted and only one remains
To completly remove a table and its content from the database use the following syntax:
DROP TABLE fruit;