Use the MySQL update statement correctly

The following article mainly introduces the actual use of MySQL update statements. We first introduce the actual implementation of MySQL update statements with a single table of UPDATE statements. The following is the detailed description of the article.

MySQL UPDATE statements 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] 

Multi-table UPDATE statement:

UPDATE [LOW_PRIORITY] [IGNORE] table_references 
SET col_name1=expr1 [, col_name2=expr2 ...] 
[WHERE where_definition] 

The UPDATE syntax updates each column in an existing table row with a new value. The SET clause indicates which columns to modify and which values to give. The WHERE clause specifies which rows should be updated. If there is no WHERE clause, update all the rows. If the ORDER BY clause is specified, the rows are updated in the order specified. The LIMIT clause is used for a given limit of 1 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 execution of UPDATE is delayed until no other client reads from the table.

If you use the IGNORE keyword, the update statement does not break even if an error occurs during the update process. If a duplicate keyword conflict occurs, these rows will not be updated. If the column is updated and the new value causes a data conversion error, the rows are updated to the closest legal value.

If you access a column through tbl_name in 1 expression, UPDATE USES the current value in the column. For example, the following statement sets the age column to 1 more than the current value:

MySQL> UPDATE persondata SET ageage=age+1; 

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

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

If you set column 1 to the value it currently contains, MySQL will notice this point but will not update it.

If you update the column 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 added up. 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 the "zero" value.

UPDATE returns the actual number of rows changed. The MySQL_info() C API function returns the number of rows matched and updated, as well as the number of warnings generated during UPDATE.

You can use LIMIT row_count to limit the scope of UPDATE. The LIMIT clause is one qualifier that matches the row. The statement is aborted whenever it finds lines row_count that satisfy the WHERE clause, whether or not the lines have been changed.

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

You can also perform UPDATE operations that include multiple tables. The table_references clause lists the tables contained 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 internal union using the comma operator, but the ES92en-ES93en UPDATE statement can use any type of union allowed in the SELECT statement, such as LEFT JOIN.

Note: You cannot use ORDER BY or LIMIT with ES103en-ES104en UPDATE.

In the 1 modified ES108en-ES109en UPDATE, some columns are referenced. You only need the MySQL UPDATE permissions for these columns. Some columns were read, but not modified. You only need SELECT permissions for these columns.

If the multiple-ES117en UPDATE statement you are using contains InnoDB tables with foreign key constraints, the MySQL optimizer may treat the tables in a different order than the order of the upper and lower hierarchies. In this case, the statement is invalid and is rolled back. At the same time, update a single 1 table and rely on ON UPDATE functionality. This feature is provided by InnoDB to modify other tables accordingly.

Currently, you cannot update 1 table in 1 subquery and select from the same table at the same time.

The above content is the introduction of MySQL update, I hope it will be helpful to your study.