MySQL UPDATE query

MySQL UPDATE query

If we need to modify or update data in MySQL, we can use the SQL UPDATE command to operate. .

Syntax

The following is the general SQL syntax for the UPDATE command to modify MySQL data 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 simultaneously in a single table.

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

Update data through the command prompt

Below we will use the WHERE clause in the SQL UPDATE command to update the specified data in the user table:

Example

The following example will update the user_title field value with user_id 3 in the data table:

mysql> UPDATE user SET user_title='学习 C++' WHERE user_id=3;Query OK, 1 rows affected (0.01 sec)
 mysql> SELECT * from user WHERE user_id=3;
+-----------+--------------+---------------+-----------------+
| user_id | user_title | user_author | submission_date |
+-----------+--------------+---------------+-----------------+
| 3         | 学习 C++   | PHP  | 2016-05-06      |
+-----------+--------------+---------------+-----------------+
 rows in set (0.01 sec)

From the results, user_id is 3 user_title has been modified.

Use PHP script to update data

Use the function mysqli_query() in PHP to execute SQL statements. You can use it in the SQL UPDATE statement or Do not use the WHERE clause.

Note: Do not use the WHERE clause to update all the data in the data table, so be careful.

This function has the same effect as executing SQL statements in the mysql> command prompt.

Example

The following example will update the data of the user_title field with runoob_id 3.