MySQL – UPDATE Query

There may be a requirement where the existing data in a MySQL table needs to be modified. You can do so 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 field altogether.
• You can specify any condition using the WHERE clause.
• You can update the values in a single table at a time.
  The WHERE clause is very useful when you want to update the 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 mobile_phone field for a record having the id as 2.

  root@host# mysql -u root -p password;
  Enter password:
  mysql> use mydb;
  Database changed
  mysql> update employees set mobile_phone = '777 333 8888' where id = 2;
  Query OK, 1 row affected (0.11 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  mysql>

  Updating Data Using a PHP Script

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

  Example

  The following example will update the mobile_phone field for a record having the id as 2.

  <?php
  $servername = "localhost";
  $username = "root";
  $password = "";
  $dbname = "mydb";
  // Create connection
  $conn = mysqli_connect($servername, $username, $password, $dbname);
  // Check connection
  if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
  }
  $sql = "update employees2 set mobile_phone = '777 333 8888' where id = 2;";
  $result = mysqli_query($conn, $sql);
  if ($result) {
  echo "data updated successfully!";
  }
  else {
  echo "Error: " . $sql . "<br>" . mysqli_error($conn);
  }
  ?>