MoonPoint Support Logo


Shop Amazon Warehouse Deals - Deep Discounts on Open-box and Used ProductsAmazon Warehouse Deals

Advanced Search
Sun Mon Tue Wed Thu Fri Sat

Sat, Nov 16, 2013 6:45 pm

Updating an entry in a MySQL Database

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.

[/software/database/mysql] permanent link

Once You Know, You Newegg AliExpress by

Shop Amazon Local - Subscribe to Deals in Your Neighborhood

Valid HTML 4.01 Transitional

Privacy Policy   Contact

Blosxom logo