MySQL UPDATE Query

If we need to modify or update the data in MySQL, we can use the SQL UPDATE command to operate. .
grammar
Following is the UPDATE command to modify the general SQL syntax for MySQL 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 separate table at the same time.

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

Update data through command prompts

Below we will update the data specified in the runoob_tbl table using the WHERE clause in the SQL UPDATE command:
Example
The following example updates the runoob_title field value of runoob_id 3 in the data table:
SQL UPDATE statement:

mysql> UPDATE runoob_tbl SET runoob_title='Study C++' WHERE runoob_id=3;
Query OK, 1 rows affected (0.01 sec)

mysql> SELECT * from runoob_tbl WHERE runoob_id=3;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
| 3         | Study C++   | RUNOOB.COM    | 2016-05-06      |
+-----------+--------------+---------------+-----------------+
1 rows in set (0.01 sec)

As a result, the runoob_title with runoob_id 3 has been modified.

Update data using PHP scripts

The function mysqli_query() is used to execute the SQL statement in PHP. You can use or not use the WHERE clause in the SQL UPDATE statement.
Note: Do not use the WHERE clause to update all data in the data table, so be careful.
The effect of this function is the same as that of executing an SQL statement in the MySQL > command prompt.
Example
The following example updates the data of the runoob_title field with runoob_id 3.
MySQL UPDATE statement test:

<?php
$dbhost = 'localhost:3306';  // mysql server host address
$dbuser = 'root';            // mysql username
$dbpass = '123456';          // mysql username password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('connection failed: ' . mysqli_error($conn));
}
// Setting Code to Prevent Chinese Scrambling
mysqli_query($conn , "set names utf8");

$sql = 'UPDATE runoob_tbl
        SET runoob_title="Study Python"
        WHERE runoob_id=3';

mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('Unable to update data: ' . mysqli_error($conn));
}
echo 'Data update is successful!';
mysqli_close($conn);
?>**

MySQL DELETE statement

You can use the DELETE FROM command of SQL to delete records from MySQL tables.
You can execute the command at the MySQL > command prompt or in a PHP script.
grammar
The following is the general syntax for deleting data from MySQL data tables by SQL DELETE statements:

DELETE FROM table_name [WHERE Clause]

• If no WHERE clause is specified, all records in the MySQL table will be deleted.
• You can specify any condition in the WHERE clause
• You can delete records once in a single table.
  The WHERE clause is very useful when you want to delete the records specified in the data table.

Delete data from the command line

Here we will use the WHERE clause in the SQL DELETE command to delete the data selected by the MySQL data table runoob_tbl.
Example
The following example deletes the record of runoob_id 3 in the runoob_tbl table:
SQL UPDATE statement:

mysql> use RUNOOB;
Database changed
mysql> DELETE FROM runoob_tbl WHERE runoob_id=3;
Query OK, 1 row affected (0.23 sec)

Use PHP scripts to delete data

PHP uses the mysqli_query() function to execute the SQL statement. You can use or not use the WHERE clause in the SQL DELETE command.
This function performs the same effect as the MySQL > command to execute the SQL command.
Example
The following PHP instance deletes the record of runoob_id 3 in the runoob_tbl table:
MySQL DELETE clause test:

<?php
$dbhost = 'localhost:3306';  // mysql server host address
$dbuser = 'root';            // mysql username
$dbpass = '123456';          // mysql username password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('connection failed: ' . mysqli_error($conn));
}
// Setting Code to Prevent Chinese Scrambling
mysqli_query($conn , "set names utf8");

$sql = 'DELETE FROM runoob_tbl
        WHERE runoob_id=3';

mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('Unable to delete data: ' . mysqli_error($conn));
}
echo 'Data deletion was successful!';
mysqli_close($conn);
?>