MySQL Update Query

---

MySQL UPDATE Query is a DML statement used to modify the data of the MySQL table within the database. In a real-life scenario, records are changed over a period of time. So, we need to make changes in the values of the tables also. To do so, it is required to use the UPDATE query.

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 field altogether.
• You can specify any condition using the WHERE clause.
• You can update the values in a single table at a time.

---

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 Edusites_tbl.

Example

root@host# mysql -u root -p password;

Enter password:*******



mysql> use EdusiteS;

Database changed



mysql> UPDATE Edusites_tbl 

   -> SET Edusite_title = 'Learning JAVA' 

   -> WHERE Edusite_id = 3;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

---

Update Data Using a PHP Script

You can use the SQL UPDATE command with or without the WHERE CLAUSE into the PHP function – mysql_query(). This function will execute the SQL command in a similar way it is executed at the mysql> prompt.

Example

<?php

   $dbhost = 'localhost:3036';

   $dbuser = 'root';

   $dbpass = 'rootpassword';

   $conn = mysql_connect($dbhost, $dbuser, $dbpass);

   

   if(! $conn ) {

      die('Could not connect: ' . mysql_error());

   }



   $sql = 'UPDATE edusites_tbl

      SET edusite_title="Learning JAVA"

      WHERE edusite_id=3';



   mysql_select_db('edusiteLS');

   $retval = mysql_query( $sql, $conn );

   

   if(! $retval ) {

      die('Could not update data: ' . mysql_error());

   }

   echo "Updated data successfully\n";

   mysql_close($conn);

?>

---