The SQL statement explains the correct use of MySQL update

  • 2020-05-12 06:22:05
  • OfStack

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] 


Multi-table UPDATE statement:


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

The UPDATE syntax can update each column in the original table row with the new value. The SET clause indicates which columns to modify and which values to assign. The WHERE clause specifies which rows should be updated. If there is no WHERE clause, all rows are updated. If the ORDER BY clause is specified, the rows are updated in the order 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, the execution of UPDATE is delayed until no other clients read from the table.

If you use the IGNORE keyword, the update statement will not break even if an error occurs during the update process. If a duplicate keyword conflict occurs, these lines will not be updated. If the column is updated and the new value causes a data conversion error, the rows are updated to the nearest valid 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 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 and then increases the age column:

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

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

If you update a 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. For numeric types, the default value is 0; For string types, the default 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 returns the number of rows that have been matched and updated, as well as the number of warnings generated during the UPDATE process.

You can limit the scope of UPDATE by using LIMIT row_count. The LIMIT clause is a qualification that matches the line. The statement terminates whenever lines row_count that satisfy the WHERE clause are found, whether or not those lines have been changed.

If an UPDATE statement includes 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 multiple-table 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 at the same time as multiple-table UPDATE.

In 1 changed multiple-table 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 the SELECT permissions for these columns.

If your multiple-table UPDATE statement contains InnoDB tables with foreign key restrictions, the order in which the MySQL optimizer processes the tables may differ from the order in the hierarchy. In this case, the statement is invalid and rolled back. At the same time, update 1 single table and rely on ON UPDATE function. This functionality is provided by InnoDB and is used to modify other tables accordingly.

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


Related articles: