Usage of mysqlupdate statement _ MySQL

This article describes in detail the usage of the update statement in mysql. The system comprehensively learns the usage of the update statement. if you need it, refer to the following: first, the 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]

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

1. if you use the LOW_PRIORITY keyword, the UPDATE execution is delayed until no other client reads from the table.

2. 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, set the age column to be greater than the current value:

The code is as follows:

Mysql> UPDATE persondata SET age = age + 1;

The UPDATE value is evaluated from left to right.

For example, double the age column and add it again:

The code is as follows:

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

Example:

The code is as follows:

SQL> UPDATE items, month SET items. price = month. price
WHERE items. id = month. id;

Note: The code above shows the 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: order by, LIMIT, and multiple-table UPDATE cannot be used simultaneously.

In a modified multiple-table UPDATE, some columns are referenced. You only need the 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.

Basic usage of update statements

A. Use 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 publishers is updated if all the publishers in publishers move their headquarters to Atlanta, Georgia.

The code is as follows:

UPDATE publishers
SET city = 'Atlanta ', state = 'ga'

In this example, the names of all publishers are changed to NULL.

The code is as follows:

UPDATE publishers
SET pub_name = NULL

You can also use the calculated value in the update. In this example, all prices in titles are doubled.

The code is as follows:

UPDATE titles
SET price = price * 2

B. use the WHERE clause with the UPDATE statement.
The WHERE clause specifies the row to be updated. for example, in the following fictitious event, the name of North California is changed to Pacifica (PC ), in Auckland, citizens voted to change the City name to Bay City. This example shows how to update the table authors for all previous residents of Auckland (their addresses are outdated.

The code is 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 residents in other cities in North California.

C. use the UPDATE statement to obtain information from another table.
In this example, modify the ytd_sales column in the titles table to reflect the latest sales records in the table sales.

The code is 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 specific item only records a batch of sales volume on a specific date, and the update is up-to-date. If this is not the case (that is, if a particular item can record more than one batch of sales on the same day), the example shown here will go wrong. The example can be correctly executed, but each item only uses a batch of sales volume to update, no matter how many batches are actually sold that day. This is because an UPDATE statement never updates the same row twice.

If more than one batch of products can be sold on the same day, the total sales volume of each product must be combined in the UPDATE statement, as shown in the following example:

The code is 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 the SELECT statement.
This example updates the state columns of the top 10 authors from the table authors.

The code is as follows:

UPDATE authors
SET state = 'zz'
FROM (select top 10 * FROM authors order by au_lname) AS t1
WHERE authors. au_id = t1.au _ id

The above is all about the usage of mysql update statements. I hope it will be helpful to you.