Shulou(Shulou.com)05/31 Report--

This article focuses on "how to modify the value of a field by mysql". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how mysql modifies the value of a field.

In mysql, the update statement can be used to modify the value of a field, and the UPDATE statement can modify the field value of a single row, a group of rows, or all rows in a data table; the syntax "UPDATE table name SET field 1 = value 1 [, field 2 = value 2 …] [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 updates existing data in a table and modifies the field values of a single row, a set of rows, or all rows in a data table.

The syntax of the MySQL UPDATE statement is explained below:

UPDATE table name SET field 1 = value 1 [, field 2 = value 2...] [WHERE condition]

In the above UPDATE statement:

First, specify the name of the table to update the data after the UPDATE keyword.

Second, the SET clause specifies the column and the new value to modify. To update multiple columns, use a comma-separated list. Provide the value to be set in the assignment of each column in the form of a literal value, expression, or subquery.

Third, use the condition in the WHERE clause to specify the row to update. The WHERE clause is optional. If you omit the WHERE clause, the UPDATE statement updates all rows in the table.

Note that the WHERE clause is very important, so you should not forget to specify the condition for the update. Sometimes you may just want to change a row; however, you may forget to write the WHERE clause, causing all rows in the table to be updated unexpectedly.

MySQL supports two modifiers in the UPDATE statement.

The LOW_PRIORITY modifier instructs the UPDATE statement to defer updates until there is no join that reads data from the table. LOW_PRIORITY takes effect for storage engines that only use table-level locking, such as MyISAM,MERGE,MEMORY.

The IGNORE modifier causes the UPDATE statement to continue to update the row even if an error occurs. Rows that cause errors, such as duplicate key conflicts, are not updated.

2. MySQL UPDATE example

We use some tables in the MySQL sample database to practice using update statements.

2.1 A single-column example of MySQL UPDATE

In this example, we will update Mary Patterson's email to the new email 123@qq.com.

First, to ensure that the email is updated successfully, query the email for Mary from the employees table using the following SELECT statement:

SELECT firstname, lastname, emailFROM employeesWHERE employeeNumber = 1056

Execute the above query statement to get the following results-

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

The second step is to use the update statement to update the Mary 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, you use the WHERE clause to specify that the row with employee number 1056 is updated. The SET clause sets the value of the e-mail column to the new e-mail.

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

SELECT firstname, lastname, emailFROM employeesWHERE employeeNumber = 1056

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

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

2.2 MySQL UPDATE multiple columns

To update values in multiple columns, you need to specify the assignment in the SET clause. For example, the following statement updates the last name and email column of employee number 1056:

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

After executing the above statement, query the record with employee number: 1056, as follows-

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

2.3 MySQL UPDATE example using the SELECT statement

You can use the select statement to query data from other tables to provide values to the SET clause.

For example, in the customers table, some customers do not have any sales representatives. The value of the salesRepEmployeeNumber column is NULL, as follows:

Mysql > SELECT customername, salesRepEmployeeNumberFROM customersWHERE 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 representatives and updates for these customers.

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

The following query randomly selects an employee whose position is Sales Rep from the employees table.

SELECT employeeNumberFROM employeesWHERE jobtitle = 'Sales Rep'ORDER BY RAND () LIMIT 1

To update the sales representative employee number (employeeNumber) column in the customers table, we put the above query in the SET clause of the UPDATE statement, 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 has a sales representative. In other words, the following query does not return any row data.

SELECT salesRepEmployeeNumberFROM customersWHERE salesRepEmployeeNumber IS NULL;'so far, I believe you have a deeper understanding of "how to modify the value of a field by mysql". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.