MySQL UPDATE statement

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

The following is the general SQL syntax for the UPDATE statement 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.

Data can be updated simultaneously in a single table.

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

Update data through command prompt

The following example will update the runoob_title field value with runoob_id 3 in the data table:

MariaDB [RUNOOB]> UPDATE runoob_tbl SET runoob_title='Learning JAVA' WHERE runoob_id=3;
Query OK , 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Use PHP script to update data

Use the function mysql_query() in PHP to execute SQL statements. You can use it in the SQL UPDATE statement or Do not use WHERE clause.

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

The following example will update the data of the runoob_title field with runoob_id 3.

<?php
$dbhost = &#39;localhost:3036&#39;;
$dbuser = &#39;root&#39;;
$dbpass = &#39;rootpassword&#39;;
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die(&#39;Could not connect: &#39; . mysql_error());
}
$sql = &#39;UPDATE runoob_tbl
        SET runoob_title="Learning PHP"
        WHERE runoob_id=3&#39;;
mysql_select_db(&#39;RUNOOB&#39;);
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die(&#39;Could not update data: &#39; . mysql_error());
}
echo "Updated data successfully\n";
mysql_close($conn);
?>