MySQL UPDATE Command

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

Syntax

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

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

• You can update one or multiple fields simultaneously.
• You can specify any condition in the WHERE clause.
• You can update data in a single table simultaneously.

The WHERE clause is very useful when you need to update data in specific rows of the table.

Below, we will use the WHERE clause in the SQL UPDATE command to update specific data in the tutorialpro_tbl table:

Example

The following example will update the tutorialpro_title field value where tutorialpro_id is 3:

SQL UPDATE Statement:

mysql> UPDATE tutorialpro_tbl SET tutorialpro_title='Learning C++' WHERE tutorialpro_id=3;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * from tutorialpro_tbl WHERE tutorialpro_id=3;
+-----------+-----------------+------------------+-----------------+
| tutorialpro_id | tutorialpro_title | tutorialpro_author | submission_date |
+-----------+-----------------+------------------+-----------------+
| 3         | Learning C++    | tutorialpro.org   | 2016-05-06      |
+-----------+-----------------+------------------+-----------------+
1 row in set (0.01 sec)

As seen from the results, the tutorialpro_title for tutorialpro_id 3 has been modified.

Updating Data Using PHP Script

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

Note: Not using the WHERE clause will update all data in the table, so be cautious.

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

Example

The following example will update the tutorialpro_title field data where tutorialpro_id is 3.

MySQL UPDATE Statement Test:

<?php
$dbhost = 'localhost';  // mysql server hostname
$dbuser = 'root';       // mysql username
$dbpass = '123456';     // mysql password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('Connection failed: ' . mysqli_error($conn));
}
// Set encoding to prevent Chinese乱码
mysqli_query($conn , "set names utf8");

$sql = 'UPDATE tutorialpro_tbl
        SET tutorialpro_title="Learning Python"
        WHERE tutorialpro_id=3';

mysqli_select_db( $conn, 'tutorialpro' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('Could not update data: ' . mysqli_error($conn));
}
echo 'Data updated successfully!';
mysqli_close($conn);
?>