MySQL UPDATE Statement

If we need to modify or update data in MySQL, we can use the SQL UPDATE command to operate.

Syntax

The following is the general SQL syntax for the UPDATE command used to modify MySQL data table data:

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

• You can update one or more fields at the same time.

• You can specify any condition in the WHERE clause.

• You can update data in a single table at the same time.

WHERE clause is very useful when you need to update data in a specified row of the data table.

Update data through command prompt

Below we will use the WHERE clause in the SQL UPDATE command to update w3specified data in the codebox_tbl table:

Online example

The following example will update data in the w3codebox_id is 3 of w3codebox_title field value:

mysql> UPDATE w3codebox_tbl SET w3codebox_title='Learn C++ WHERE w3codebox_id=3;
Query OK,　1　rows affected (0.01　sec)
　
mysql> SELECT　*　from w3codebox_tbl WHERE w3codebox_id=3;
+-----------+--------------+---------------+-----------------+
| w3codebox_id        | w3codebox_title     | w3codebox_author     | submission_date |
+-----------+--------------+---------------+-----------------+
|　3　　　　　　　　　| Learn C++　　　| oldtoolbag.com       |　2016-05-06　　　　　　|
+-----------+--------------+---------------+-----------------+
1　rows in set (0.01　sec)

From the results, w3codebox_id is 3 of w3codebox_title has been modified.

Use PHP script to update data

In PHP, use the function mysqli_query() to execute SQL statements, you can use or not use the WHERE clause in the SQL UPDATE statement.

Note:Updating all data in the data table without using the WHERE clause is therefore cautious.

This function has the same effect as executing SQL statements in the mysql> command prompt.

Online example

The following example will update w3codebox_id is 3 of w3data of the codebox_title field.

<?php
$dbhost = 'localhost';　　//　MySQL server host address
$dbuser = 'root';　　　　　　　　　　　　//　MySQL username
$dbpass = '123456';　　　　　　　　　　//　MySQL username and password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
　　　　die('Connection failed: ' . mysqli_error($conn));
}
//　Set encoding to prevent Chinese garbled characters
mysqli_query($conn, "set names utf8");
　
$sql = 'UPDATE w3codebox_tbl
　　　　　　　　SET w3codebox_title="Learn Python"
　　　　　　　　WHERE w3codebox_id=3';
　
mysqli_select_db($conn, 'w';3codebox');
$retval = mysqli_query($conn, $sql);
if(! $retval )
{
　　　　die('Unable to update data: ' . mysqli_error($conn));
}
echo 'Data update successful!';
mysqli_close($conn);
?>