Correct use of Mysql UPDATE statement _mysql

The following article is mainly about the actual use of the MySQL UPDATE statement, we first is a single table UPDATE statement to the implementation of the MySQL UPDATE statement of the actual scheme, the following is the detailed description of the article.

Single-table MySQL UPDATE statement:

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 ...] 

The update syntax can update columns in existing table rows with new values. The SET clause indicates which columns to modify and what values to give. The WHERE clause specifies which rows should be updated. If there is no WHERE clause, all rows are updated. If an ORDER BY clause is specified, the row is updated in the sequence specified. The limit clause is used to limit the number of rows that can be updated, given a limit.

The MySQL UPDATE statement supports the following modifiers:

If you use the Low_priority keyword, update execution is deferred until no other client reads from the table.

If you use the Ignore keyword, the UPDATE statement is not interrupted even if an error occurs during the update process. If a duplicate keyword conflict occurs, the rows are not updated. If the columns are updated and the new values cause data conversion errors, the rows are updated to the nearest legitimate 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 one more than the current value:

mysql> UPDATE persondata SET ageage=age+1; 

MySQL update assignment is evaluated from left to right. For example, the following statement doubles the age column and then increases it:

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

If you set a column to a value that it currently contains, 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 types, the default value is 0; for string types, the default value is an empty string ('); for date and time types, the default value is ' zero '.

Update returns the number of rows that were actually changed. The Mysql_info () C API function can return the number of rows that are matched and updated, and the number of warnings generated during the update process.

You can use limit Row_count to qualify the scope of the update. A limit clause is a qualifier that matches a row. The statement aborts whenever a row_count row is found that satisfies the WHERE clause, regardless of whether the rows are changed or not.

If an UPDATE statement includes an ORDER BY clause, the row is updated in the sequence specified by the child sentence.

You can also perform an update operation that includes multiple tables. The table_references clause lists the tables that are included in the Union. Here is an example:

Sql>update Items,month SET Items.price=month.price
WHERE items.id=month.id;
The above example shows an inner union using the comma operator, but the Multiple-table UPDATE statement can use any type of union allowed in the SELECT statement, such as a left JOIN.

Note: You cannot use the order by or limit with Multiple-table update.

In a changed multiple-table update, some columns are referenced. You only need MySQL update permissions for these columns. Some of the columns have been read, but have not been modified. You only need SELECT permissions for these columns.

If you are using a multiple-table UPDATE statement that contains a InnoDB table with a FOREIGN key constraint, the order of the MySQL optimizer processing tables may differ from the order of the upper and lower levels relationships. In this case, the statement is invalid and is rolled back. Also, update a single table and rely on the on Update feature. This feature is provided by InnoDB and is used to modify the other tables accordingly.

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

The above related content is the introduction of MySQL update, I hope to help you learn.