MySQL UPDATE query

MySQL UPDATE query

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

Grammar

Here is the general SQL syntax for the UPDATE command to modify mySQL data sheet 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 the data at the same time in a separate table.

The WHERE clause is useful when you need to update the data for the specified rows in the data table.

Here's what we'll do with the WHERE clause in the SQL UPDATE command to w3cschool_tbl the data specified in the table:

Instance

The following example updates the w3cschool_id field value of 3 w3cschool_title data table:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use W3CSCHOOL;
Database changed
mysql> UPDATE w3cschool_tbl 
    -> SET w3cschool_title='Learning JAVA' 
    -> WHERE w3cschool_id=3;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

Update the data using the PHP script

PHP uses mysql_query () to execute SQL statements, which you can use in SQL UPDATE statements or not.

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

Instance

The following example updates the w3cschool_id of the w3cschool_title field of 3.

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
} 
$sql = 'UPDATE w3cschool_tbl
        SET w3cschool_title="Learning JAVA"
        WHERE w3cschool_id=3';

mysql_select_db('W3CSCHOOL');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not update data: ' . mysql_error());
}
echo "Updated data successfully\n";
mysql_close($conn);
?>