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 MySQLUPDATEstatement is explained below:

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

In the aboveUPDATEstatement:

• First , specify the table name to update data after theUPDATEkeyword.
• Secondly, theSETclause 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 theWHERE clauseto specify the rows to be updated. TheWHEREclause is optional. If theWHEREclause is omitted, theUPDATEstatement updates all rows in the table.

Please note that theWHEREclause 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 theWHEREclause, causing all rows in the table to be accidentally updated.

MySQL supports two modifiers in theUPDATEstatement. The

• LOW_PRIORITYmodifier instructs theUPDATEstatement to delay updates until there are no connections to read data from the table.LOW_PRIORITYEffective forstorage enginesthat only use table-level locking (such asMyISAM,MERGE,MEMORY).
• TheIGNOREmodifier allows theUPDATEstatement 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 theUPDATEstatement.

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 theemployeestable:

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 theUPDATEstatement 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 theWHEREclause to specify the updated It is the row with employee number1056. TheSETclause sets the value of the email column to the new email.

Third, execute theSELECTstatement 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 theSETclause. 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 theSELECTstatement to provideSETThe value of the clause.

For example, in thecustomerstable, some customers do not have any sales representatives. ThesalesRepEmployeeNumbercolumn has a value ofNULLas 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 Repfrom theemployeestable and update it to theemployeestable .
The following query statement randomly selects an employee whose position isSales Repfrom theemployeestable.

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

To update the sales representative employee number (employeeNumber) column in thecustomerstable, we place the above query at the end of theUPDATEstatementSETclause, 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 thecustomerstable 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!