How to modify the value of a field in mysql

In mysql, you can use the UPDATE statement to modify the field value. The UPDATE statement can modify the field value of a single row, a group of rows, or all rows in the data table; the syntax "UPDATE table name SET field 1 = value 1[,field2=value2...] [WHERE condition]".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In mysql, you can use the UPDATE statement to modify the value of a field.

The UPDATE statement can update existing data in the table, and can modify the field values ​​of a single row, a group of rows, or all rows in the data table.

The syntax of the MySQL UPDATE statement is explained below:

UPDATE 表名 
SET 
  字段1=值1[,字段2=值2…] 
[WHERE 条件]

In the above UPDATE statement:

• First , specify the table name to update data after the UPDATE keyword.
• Secondly, the SET clause specifies the column to be modified and the new value. To update multiple columns, use a comma-separated list. Provide the value to be set in the assignment of each column as a literal, expression, or subquery.
• Third, use the conditions in the WHERE clause to specify the rows to be updated. The WHERE clause is optional. If the WHERE clause is omitted, the UPDATE statement updates all rows in the table.

Please note that the WHERE clause is very important, so you should not forget to specify the conditions for the update. Sometimes, you may only want to change a single row; however, you may forget to write the WHERE clause, causing all rows in the table to be accidentally updated.

MySQL supports two modifiers in the UPDATE statement. The

• LOW_PRIORITY modifier instructs the UPDATE statement to delay updates until there are no connections to read data from the table. LOW_PRIORITYEffective for storage engines that only use table-level locking (such as MyISAM, MERGE, MEMORY).
• The IGNORE modifier allows the UPDATE statement to continue updating rows even if an error occurs. Rows causing errors (such as duplicate key violations) are not updated.

2. MySQL UPDATE Example

We use some tables in the MySQL sample database to practice using the UPDATE statement.

2.1 MySQL UPDATE a single column example

In this example, we will update Mary Patterson’s email to a new email123@qq.com.

First, in order to ensure that the updated email is successful, use the following SELECT statement to query Mary's email from the employees table:

SELECT 
    firstname, lastname, email
FROM
    employees
WHERE
    employeeNumber = 1056;

Execute the above Query statement and get the following results-

+-----------+-----------+----------------------+
| firstname | lastname  | email                |
+-----------+-----------+----------------------+
| Mary      | Patterson | mpatterso@qq.com     |
+-----------+-----------+----------------------+
1 row in set

The second step, use the UPDATE statement to update Mary’s email to a new email : 123@qq.com, as shown in the following query:

UPDATE employees 
SET 
    email = '123@qq.com'
WHERE
    employeeNumber = 1056;

Because in the above statement, you only want to update one row, use the WHERE clause to specify the updated It is the row with employee number 1056. The SET clause sets the value of the email column to the new email.

Third , execute the SELECT statement again to verify the changes.

SELECT 
    firstname, lastname, email
FROM
    employees
WHERE
    employeeNumber = 1056;

Execute the above query statement again and get the following results-

+-----------+-----------+---------------------+
| firstname | lastname  | email               |
+-----------+-----------+---------------------+
| Mary      | Patterson | 123@qq.com          |
+-----------+-----------+---------------------+
1 row in set

2.2 MySQL UPDATE multiple columns

To update the values ​​in multiple columns, The allocation needs to be specified in the SET clause. For example, the following statement updates the last name and email columns of employee number 1056:

UPDATE employees 
SET 
    lastname = 'Hill',
    email = 'mary.hill@qq.com'
WHERE
    employeeNumber = 1056;

After executing the above statement, query the records with employee number: 1056, As shown below -

+-----------+----------+----------------------+
| firstname | lastname | email                |
+-----------+----------+----------------------+
| Mary      | Hill     | mary.hill@qq.com     |
+-----------+----------+----------------------+
1 row in set

2.3 MySQL UPDATE example using SELECT statement

Data from other tables can be queried using the SELECT statement to provide SETThe value of the clause.

For example, in the customers table, some customers do not have any sales representatives. The salesRepEmployeeNumber column has a value of NULL as shown below:

mysql> SELECT 
    customername, salesRepEmployeeNumber
FROM
    customers
WHERE
    salesRepEmployeeNumber IS NULL;
+--------------------------------+------------------------+
| customername                   | salesRepEmployeeNumber |
+--------------------------------+------------------------+
| Havel & Zbyszek Co             | NULL                   |
| Porto Imports Co.              | NULL                   |
| Asian Shopping Network, Co     | NULL                   |
| Natrlich Autos                 | NULL                   |
| ANG Resellers                  | NULL                   |
| Messner Shopping Network       | NULL                   |
| Franken Gifts, Co              | NULL                   |
| BG&E Collectables              | NULL                   |
| Schuyler Imports               | NULL                   |
| Der Hund Imports               | NULL                   |
| Cramer Spezialitten, Ltd       | NULL                   |
| Asian Treasures, Inc.          | NULL                   |
| SAR Distributors, Co           | NULL                   |
| Kommission Auto                | NULL                   |
| Lisboa Souveniers, Inc         | NULL                   |
| Stuttgart Collectable Exchange | NULL                   |
| Feuer Online Stores, Inc       | NULL                   |
| Warburg Exchange               | NULL                   |
| Anton Designs, Ltd.            | NULL                   |
| Mit Vergngen & Co.             | NULL                   |
| Kremlin Collectables, Co.      | NULL                   |
| Raanan Stores, Inc             | NULL                   |
+--------------------------------+------------------------+
22 rows in set

We can provide sales reps and updates for these customers.

To do this, you need to randomly select an employee with the position Sales Rep from the employees table and update it to the employees table .
The following query statement randomly selects an employee whose position is Sales Rep from the employees table.

SELECT 
    employeeNumber
FROM
    employees
WHERE
    jobtitle = 'Sales Rep'
ORDER BY RAND()
LIMIT 1;

To update the sales representative employee number (employeeNumber) column in the customers table, we place the above query at the end of the UPDATE statement SET clause, as follows:

UPDATE customers 
SET 
    salesRepEmployeeNumber = (SELECT 
            employeeNumber
        FROM
            employees
        WHERE
            jobtitle = 'Sales Rep'
        LIMIT 1)
WHERE
    salesRepEmployeeNumber IS NULL;

If you query the data in the customers table after executing the above update statement, you will see that each customer There is a sales representative. In other words, the following query returns no row data.

SELECT 
     salesRepEmployeeNumber
FROM
    customers
WHERE
    salesRepEmployeeNumber IS NULL;
`

[Related recommendations: mysql video tutorial]

The above is the detailed content of How to modify the value of a field in mysql. For more information, please follow other related articles on the PHP Chinese website!