Updating Columns with Formulas


UPDATE loan SET date_lent = DATE_ADD(date_lent, INTERVAL 1 YEAR) WHERE date_lent = '2005-01-01'; 



Assume that in the wee hours of the New Year, you loaned visiting friends five books, recorded the loans in a table called loan, and accidentally entered the wrong year in the date stored in date_lent. The simple UPDATE statement in the phrasebox allows you to correct the issue for all five rows in one query.

The formula used in the previous sample query is simple enougha new date is calculated by using MySQL's DATE_ADD function to add a week to an existing date. The interesting thing is that the new value assigned to date_lent is based on the existing value of date_lent. The feature of UPDATE that allows the query to reference existing values in the table is part of what makes this type of query so useful.



MySQL Phrasebook. Essential Code and Commands
MySQL Phrasebook
ISBN: 0672328399
EAN: 2147483647
Year: 2003
Pages: 130

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