Mysql update query statement with example

In this tutorial, we will learn about the update query in MySQL. The update statement is used to update the existing row data in the table using where condition. This is the most used MySQL statement. In this tutorial, we will learn how to update a single row as well as multiple rows.
Syntax:

Update table_name set col_name = value where where_condition

In the above syntax set clause is used to modify the column with the given value. the where clause, if specified, is used to identify which rows to update.

In this tutorial, we will learn how to update data using the terminal. Open your terminal and login to your MySQL server using the following command.

root@desert:~$ mysql -u root -p
Enter password: *******

mysql> use dummyDatabase;
Database changed

Update single column

In this section, we will use update statement to set a single record. Below is the example of the update statement.

UPDATE products 
   -> SET product_name = 'some product name' 
   -> WHERE id = 14;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

In the above statement where condition is used to update the selected row with id 14.

Update multiple columns

In this section, we will use the update statement to set multiple records. Below is the example of the multiple update query.

update products
    -> set product_name = 'some other product', price = 2500
    -> where id = 14;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Update statement using PHP Script

In this section, we will take an example to update the table using PHP script using PHP inbuilt function mysqli_query(). The mysqli_query takes two-parameter connection and sql query

<?php
$databaseHost = "localhost";
$databaseUser = "root";
$databasePwd = "root";
$databaseName = 'dummyDatabase';

$connection = mysqli_connect($databaseHost, $databaseUser, $databasePwd, $databaseName);

// check if mysql connection established.
if (mysqli_connect_errno()) {
	die ("Could not connect to database: " . mysqli_connect_error());
}

// preparing update statement
$sql = "UPDATE products SET product_name = 'product1' WHERE id = 14";
$response = mysqli_query($connection, $sql);

if (!$response) {
	die ("Failed to update: " . mysqli_error($connection));
}
echo "Table successfully udpated.\n";
mysqli_close($connection);
?>