The
UPDATE command allows one to update an existing entry in
a MySQL database. The format for the command is as follows:
UPDATE table_name SET column1=value1, column2=value2, columnN=valueN WHERE clause;
E.g., suppose there is a database named "software" with a table named "system1", which is used to maintain records of all the software installed on system1, with the fields id, which ia a numeric value, and a character field named "developer". The following commands could be used to update just the developer field for the record in the table with id=37.
mysql> use software; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> update system1 set developer="Acme Corporation" where id=37; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
You can enter a date for a date field with the value enclosed in single
quotes in the form YYYY-MM-DD. E.g.:
update system1 set Obtained='2013-11-14' where id=37;
You can calculate the value for a field based on the contents of another field in the database. E.g., suppose there is a field named Obtained in the database and anotehr column with a field indicating when a virus scan was conducted named Scan_date. If I knew a particular piece of software was scanned the day after it was obtained, I could use the command below:
update system1 set Scaned=Obtained+1 where id=37;
What if there was no date previously entered in the Obtained field, i.e, it has a null entry for that field. Then the Scaned field will still be null.
Note: it is critically important not to forget the WHERE
clause when using an UPDATE command. If you forget it, then the
update will be applied to every record in the database and you won't be able
to undo the update command unless you've followed the steps listed at
13.3.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax prior to applying
the update command. If you haven't taken such steps, unless you have a
backup you can rely on, you won't be able to revert your data to what
it was before you applied the update command.
