MySQL – Update Query – Tutorialspoint

There may be a requirement that existing data in a MySQL table must be modified. You can do this by using the SQL UPDATE command. This will modify any field value of any MySQL table.

Syntax

The following code block has a generic SQL syntax of the UPDATE command to modify the data in the

MySQL table: UPDATE table_name SET field1 = new-value1, field2 = new-value2 [WHERE clause] You can update

  • one or more fields completely
  • . You can

  • specify any condition by using the WHERE clause
  • .

  • You can update values for only one table at a time.

The WHERE clause

is very useful when you want

to update selected rows in a table. Updating data from the command

prompt

This will use the SQL UPDATE command with the WHERE clause to update the selected data in the MySQL table tutorials_tbl

.

Example

The following example will update the tutorial_title field for a record that has the tutorial_id as 3.

root@host# password mysql -u root -p; Enter password:******* mysql> use TUTORIALS; Changed database mysql> UPDATE tutorials_tbl -> SET tutorial_title = ‘Learning JAVA’ -> WHERE tutorial_id = 3; Query successful, 1 row affected (0.04 sec) Matching rows: 1 Changed: 1 Warnings: 0 mysql>

Refreshing data using a PHP script PHP uses the

mysqli query() or mysql_query() function to update records in a MySQL table. This function takes two parameters and returns TRUE in case of success or FALSE in case of error.

Syntax

$mysqli->query($sql,$resultmode) Sr.No. Parameter and Description 1

$sql

Required – SQL query to update records in a MySQL table

. 2

$resultmode

Optional – Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

Try the following example

to update a record in a table

Copy and paste the following example as mysql_example.php −

<html> <head> <title>Updating MySQL Table</title> </head> <body> <?php $dbhost = ‘localhost’; $dbuser = ‘root’; $dbpass = ‘root@123’; $dbname = ‘TUTORIALS’; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if($mysqli->connect_errno ) { printf(“Connection error: %s<br />”, $mysqli->connect_error); exit(); } printf(‘Successfully connected.<br />’); if ($mysqli->query(‘UPDATE tutorials_tbl set tutorial_title = “Learning Java” where tutorial_id = 4’)) { printf(“Table tutorials_tbl updated successfully.<br />”); } if ($mysqli->errno) { printf(“Could not update table: %s<br />”, $mysqli->error); } $sql = “SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl”; $result = $mysqli->query($sql); if ($result->num_rows > 0) { while($row = $result->fetch_assoc()) { printf(“Id: %s, Title: %s, Author: %s, Date: %d <br />”, $row[“tutorial_id”], $row[“tutorial_title”], $row[“tutorial_author”], $row[“submission_date”]); } } else { printf(‘No record found.<br />’); } mysqli_free_result($result); $mysqli->close(); ?> </body> </html>

Output

Access the deployed mysql_example.php on the Apache web server and verify the output. Here we have entered several records in the table before running the select script.

Connected successfully. The table tutorials_tbl updated successfully. Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021 Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021 Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021 Id: 4, Title: Learning Java, Author: Mahesh, Date: 2021 Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021

Contact US