Detailed usage of MySQL UPDATE statement _mysql

First, a single table UPDATE statement:

UPDATE [low_priority] [IGNORE] Tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE Where_definition]
[Order BY ...]
[LIMIT Row_count]

Second, the Multiple-table UPDATE statement:

UPDATE [low_priority] [IGNORE] Table_references
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE Where_definition]

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 UPDATE statement supports the following modifiers:

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

2, 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, 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, set the age column to one more than the current value:

Copy Code code as follows:

mysql> UPDATE persondata SET age=age+1;

The update assignment is evaluated from left to right.

For example, double the age column and then increase it:

Copy Code code as follows:

mysql> UPDATE persondata SET age=age*2, age=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.

Example:

Copy Code code as follows:

Sql>update Items,month SET Items.price=month.price
WHERE items.id=month.id;

Description: The above code 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 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.

Several basic usages of the UPDATE statement

A. Using simple UPDATE

The following example shows how all rows are affected if the WHERE clause is removed from the UPDATE statement.

The following example shows how table publishers is updated if all publishers in the table publishers move their headquarters to Georgia State's Atlanta.

Copy Code code as follows:

UPDATE Publishers
SET city = ' Atlanta ', state = ' GA '

This example changes the names of all publishers to NULL.

Copy Code code as follows:

UPDATE Publishers
SET pub_name = NULL

You can also use calculated values in updates. This example doubles all the prices in table titles.

Copy Code code as follows:

UPDATE titles
SET Price = Price * 2

B. Use the WHERE clause with the UPDATE statement
The WHERE clause specifies the row to update for example, in the following fictional event, Northern California was renamed Pacifica (abbreviated PC), while Oakland's citizens voted to change the city's name to Bay. This example shows how to update table authors for all previous residents of Oakland, whose addresses are obsolete.

Copy Code code as follows:

UPDATE Authors
SET state = ' PC ', city = ' Bay City '
WHERE state = ' CA ' and city = ' Oakland '

You must write another statement to change the state name of the residents of other cities in Northern California.

C. Using information from another table through an UPDATE statement
This example modifies the Ytd_sales column in table titles to reflect the most recent sales record in table sales.

Copy Code code as follows:

UPDATE titles
SET ytd_sales = titles.ytd_sales + sales.qty
From titles, sales
WHERE titles.title_id = sales.title_id
and sales.ord_date = (SELECT MAX (sales.ord_date) from sales)

This example assumes that a particular product records only a batch of sales on a specific date, and that the update is up-to-date. If this is not the case (that is, if a particular item can record more than one sale on the same day), the example shown here will be an error. Examples can be executed correctly, but each item is updated with only a batch of sales, regardless of how many batches were actually sold that day. This is because an UPDATE statement will never update the same row two times.

For a particular product to sell more than one batch on the same day, all sales for each item must be aggregated in the UPDATE statement, as shown in the following example:

Copy Code code as follows:

UPDATE titles
SET ytd_sales =
(SELECT SUM (qty)
From sales
WHERE sales.title_id = titles.title_id
and Sales.ord_date in (SELECT MAX (ord_date) from sales)
From titles, sales

D. Use the UPDATE statement with the TOP clause in a SELECT statement
This example updates the state column for the first 10 authors from the table authors.

Copy Code code as follows:

UPDATE Authors
SET state = ' ZZ '
From (SELECT top * FROM authors order by au_lname) as T1
WHERE authors.au_id = t1.au_id

The above is the full content of the MySQL UPDATE statement usage, I hope to help you.