In-depth exploration of MySQL UPDATE statement UPDATE

The following article mainly introduces the actual usage of the MySQL update statement. We first use the UPDATE statement of a single table to introduce the actual solution for implementing the MySQL update statement. The following is the detailed description of the article, I hope you will get something better after reading it.

MySQL UPDATE statement for a single table:

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name

SET col_name1 = expr1 [, col_name2 = expr2...]

[WHERE where_definition]

[Order by...]

[LIMIT row_count]

UPDATE statements for multiple tables:

UPDATE [LOW_PRIORITY] [IGNORE] table_references

SET col_name1 = expr1 [, col_name2 = expr2...]

[WHERE where_definition]

 

The UPDATE syntax uses the new value to UPDATE columns in the original table rows. The SET clause indicates the columns to be modified and the values to be given. The WHERE clause specifies the rows to be updated. If there is no WHERE clause, all rows are updated. If the order by clause is specified, the row is updated in the specified ORDER. The LIMIT clause is used to specify a LIMIT to LIMIT the number of rows that can be updated.

The MySQL UPDATE Statement supports the following modifiers:

If you use the LOW_PRIORITY keyword, the UPDATE execution is delayed until no other clients read from the table.

If you use the IGNORE keyword, the update statement will not be interrupted even if an error occurs during the update process. If duplicate keyword conflicts occur, these rows are not updated. If the column is updated, the new value will cause data conversion errors, and these rows will be updated to the closest legal value.

If you access a column through tbl_name in an expression, UPDATE uses the current value in the column. For example, the following statement sets the age column to be greater than the current value:

 

MySQL> UPDATE persondata SET ageage = age + 1;

MySQL UPDATE values are evaluated from left to right. For example, the following statement doubles the age column and then adds it:

MySQL> UPDATE persondata SET ageage = age * 2, ageage = age + 1;

If you set a column to its current value, MySQL will notice this, but will not update it.

If you update a column that has been defined as not null to NULL, the column is set to the default value corresponding to the column type, and the number of warnings is accumulated. For numeric type, the default value is 0. For string type, the default value is null string (''). For date and time types, the default value is" zero.

UPDATE returns the number of actually changed rows. The MySQL_info () c api function returns the number of matched and updated rows and the number of warnings generated during the UPDATE process.

You can use LIMIT row_count to LIMIT the UPDATE range. The LIMIT clause is a LIMIT for matching rows. As long as the row_count rows that can satisfy the WHERE clause are found, the statement is aborted, regardless of whether these rows are changed.

If an UPDATE statement contains an order by clause, the row is updated in the ORDER specified BY the clause.

You can also perform UPDATE operations on multiple tables. The table_references clause lists the tables contained in the Union. The following is an example:

 

SQL> UPDATE items, month SET items. price = month. price

WHERE items. id = month. id;

 

The preceding example shows the internal Union using the comma operator, but the multiple-table UPDATE statement can use any types of Union allowed in the SELECT statement, such as left join.

Note: you cannot use order by, LIMIT, or multiple-table UPDATE simultaneously.

In a modified multiple-table UPDATE, some columns are referenced. You only need the MySQL UPDATE permission for these columns. Some columns are read but not modified. You only need the SELECT permission for these columns.

If your multiple-table UPDATE statement contains an InnoDB table with foreign key restrictions, the order in which the MySQL optimizer processes the table may be different from that in the upper-lower-level relationship. In this case, the statement is invalid and rolled back. At the same time, UPDATE a single table and rely ON the on update function. This function is provided by InnoDB to modify other tables.

Currently, you cannot update a table in a subquery and select from the same table.