How to Update a Table in MySQL

A database helps to keep your information in order. The MySQL database management system helps you keep the hundreds or even thousands of records in your database organized and accessible with ease. Depending on the nature of the information in your database, you may need to update your data from time to time. Prices in a store change; students' grades need updating. You can change the values in your tables by using the UPDATE query. This SQL statement allows you to modify the information contained in your tables.

Instructions

1. Open your PHP file. Locate the the code that connects to your MySQL database. Check the variable that you have used to connect to it. For example, if your connection statement is "$connect = mysql_connect("localhost', 'mysql_username', 'mysql_password');" then "$connect" is your variable.

2. Type a statement to connect to your database on a new line. Use the "mysql_select_db("database_name", $connect);" query to select the database you want to update information from.

3. Enter "mysql_query("UPDATE table_name SET table_column='new_value' WHERE table_column='number'") or die(mysql_error());" to update your table. For example, to change the price of an item in a store's database, you would type "mysql_query("UPDATE prices SET cost='3.99' WHERE item='Milk'") or die(mysql_error());".

4. Close the connection to the database by typing "mysql_close($connect);" to stop modifying it.

5. Upload your PHP file to your server and run the page to execute the UPDATE query.

Tips & Warnings

• You can update information in multiple tables by substituting "table_name" with "table_name1, table_name2, ..." for as many tables as you want to update.

• Exclude the "WHERE" clause in the query to update each entry with a new value.

• Do not confuse single quotation marks with double quotation marks or you will generate an error.

References

• MySQL: Update Syntax
• Tizag: MySQL Update
• W3 Schools: PHP MySQL Update