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 MySQLUPDATE
statement is explained below:
UPDATE 表名 SET 字段1=值1[,字段2=值2…] [WHERE 条件]
In the aboveUPDATE
statement:
UPDATE
keyword.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.WHERE
clause is optional. If theWHERE
clause is omitted, theUPDATE
statement updates all rows in the table.Please note that theWHERE
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 theWHERE
clause, causing all rows in the table to be accidentally updated.
MySQL supports two modifiers in theUPDATE
statement. The
LOW_PRIORITY
modifier instructs theUPDATE
statement to delay updates until there are no connections to read data from the table.LOW_PRIORITY
Effective forstorage enginesthat only use table-level locking (such asMyISAM,MERGE,MEMORY).We use some tables in the MySQL sample database to practice using theUPDATE
statement.
2.1 MySQL UPDATE a single column example
In this example, we will updateMary 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 queryMary
's email from theemployees
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 theUPDATE
statement to updateMary
’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 theWHERE
clause to specify the updated It is the row with employee number1056
. TheSET
clause sets the value of the email column to the new email.
Third, execute theSELECT
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 theSET
clause. For example, the following statement updates the last name and email columns of employee number1056
:
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 theSELECT
statement to provideSET
The value of the clause.
For example, in thecustomers
table, some customers do not have any sales representatives. ThesalesRepEmployeeNumber
column has a value ofNULL
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 positionSales Rep
from theemployees
table and update it to theemployees
table .
The following query statement randomly selects an employee whose position isSales Rep
from theemployees
table.
SELECT employeeNumber FROM employees WHERE jobtitle = 'Sales Rep' ORDER BY RAND() LIMIT 1;
To update the sales representative employee number (employeeNumber
) column in thecustomers
table, we place the above query at the end of theUPDATE
statementSET
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 thecustomers
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!