Using the UPDATE Command to Modify Records


Using the UPDATE Command to Modify Records

UPDATE is the SQL command used to modify the contents of one or more columns in an existing record or set of records. The most basic UPDATE syntax looks like this:

UPDATE table_name SET column1='new value', column2='new value2' [WHERE some_condition_is_true]


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 single or double quotes, escaping where necessary.

For example, assume you have a table called fruit containing an ID, a fruit name, and the status of the fruit (ripe or rotten):

mysql> SELECT * FROM fruit; +----+------------+--------+ | id | fruit_name | status | +----+------------+--------+ |  1 | apple      | ripe   | |  2 | orange     | rotten | |  3 | grape      | ripe   | |  4 | banana     | rotten | +----+------------+--------+ 4 rows in set (0.00 sec)


To update the status of the fruit to ripe, use

mysql> UPDATE fruit SET status = 'ripe'; Query OK, 2 rows affected (0.00 sec) Rows matched: 4  Changed: 2  Warnings: 0


Take a look at the result of the query. It was successful, as you can tell from the Query OK message. Also note that only two rows were affectedif you try to set the value of a column to the value it already is, the update won't occur for that column.

The second line of the response shows that four rows were matched, and only two were changed. If you're wondering what matched, the answer is simple: Because you did not specify a particular condition for matching, the match would be all rows.

You must be careful and use a 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:

mysql> UPDATE fruit SET fruit_name = 'grape'; Query OK, 4 rows affected (0.00 sec) Rows matched: 4  Changed: 4  Warnings: 0


When you read the result, you should be filled with dread: 4 of 4 records were changed, meaning your fruit table now looks like this:

mysql> SELECT * FROM fruit; +----+------------+--------+ | id | fruit_name | status | +----+------------+--------+ |  1 | grape      | ripe   | |  2 | grape      | ripe   | |  3 | grape      | ripe   | |  4 | grape      | ripe   | +----+------------+--------+ 4 rows in set (0.00 sec)


All your fruit records are now grapes. While attempting to correct the spelling of one field, all fields were changed because no condition was specified! When doling out UPDATE privileges to your users, think about the responsibility you're giving to someoneone wrong move and your entire table could be grapes.

Conditional UPDATEs

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 AND.

Assume your fruit table has not been completely filled with grapes but instead contains four records, one with a spelling mistake (grappe instead of grape). The UPDATE statement to fix the spelling mistake would be

mysql> UPDATE fruit SET fruit_name = 'grape' WHERE fruit_name = 'grappe'; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0


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:

mysql> SELECT * FROM fruit; +----+------------+--------+ | id | fruit_name | status | +----+------------+--------+ |  1 | apple      | ripe   | |  2 | pear       | ripe   | |  3 | banana     | ripe   | |  4 | grape      | ripe   | +----+------------+--------+ 4 rows in set (0.00 sec)


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 example:

mysql> SELECT * FROM grocery_inventory; +----+-------------------------+-------------------------+------------+----------+ | id | item_name               | item_desc               | item_price | curr_qty | +----+-------------------------+-------------------------+------------+----------+ | 1  | Apples                  | Beautiful, ripe apples. | 0.25       | 1000     | | 2  | Bunches of Grapes       | Seedless grapes.        | 2.99       | 500      | | 3  | Bottled Water (6-pack)  | 500ml spring water.     | 2.29       | 250      | | 4  | Bottled Water (12-pack) | 500ml spring water.     | 4.49       | 500      | | 5  | Bananas                 | Bunches, green.         | 1.99       | 150      | | 6  | Pears                   | Anjou, nice and sweet.  | 0.5        | 500      | | 7  | Avocado                 | Large Haas variety.     | 0.99       | 750      | +----+-------------------------+-------------------------+------------+----------+ 7 rows in set (0.00 sec)


When someone purchases a product, 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 1:

mysql> UPDATE grocery_inventory SET curr_qty = curr_qty - 1 WHERE id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0


This should give you a new value of 999 in the curr_qty column, and indeed it does:

mysql> SELECT * FROM grocery_inventory; +----+-------------------------+-------------------------+------------+----------+ | id | item_name               | item_desc               | item_price | curr_qty | +----+-------------------------+-------------------------+------------+----------+ | 1  | Apples                  | Beautiful, ripe apples. | 0.25       | 999      | | 2  | Bunches of Grapes       | Seedless grapes.        | 2.99       | 500      | | 3  | Bottled Water (6-pack)  | 500ml spring water.     | 2.29       | 250      | | 4  | Bottled Water (12-pack) | 500ml spring water.     | 4.49       | 500      | | 5  | Bananas                 | Bunches, green.         | 1.99       | 150      | | 6  | Pears                   | Anjou, nice and sweet.  | 0.5        | 500      | | 7  | Avocado                 | Large Haas variety.     | 0.99       | 750      | +----+-------------------------+-------------------------+------------+----------+ 7 rows in set (0.00 sec)





Sams Teach Yourself PHP, MySQL And Apache All in One
Sams Teach Yourself PHP, MySQL and Apache All in One (3rd Edition)
ISBN: 0672328739
EAN: 2147483647
Year: 2004
Pages: 327

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net