Mysql update statement usage Summary

In mysql, the update statement is used to update the records of tables in the specified database. It can also be updated in batches. It can be used with the mysql update where condition to specify the data to be updated, next I will introduce you to you.

Basic usage of MySQL update statements

Update database data

The UPDATE statement is used to modify data in a database table.

Syntax

The Code is as follows:	Copy code
UPDATE table_name SET column_name = new_value WHERE column_name = some_value

Example

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 be greater than the current value:

The Code is as follows:	Copy code
MySQL> UPDATE persondata SET ageage = age + 1;

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

The Code is as follows:	Copy code
MySQL> UPDATE persondata SET ageage = age * 2, ageage = age + 1;

You can also perform UPDATE operations on multiple tables. The table_references clause lists the tables contained in the Union. The following is an example:

 

The Code is as follows:	Copy code
SQL> UPDATE items, month SET items. price = month. price WHERE items. id = month. id;

Update statement for multi-Table update

Assume that we have two tables. One Table stores Product information for the Product table, with the Product Price column Price. The other table is the ProductPrice table, update the Price field in the ProductPrice table to 80% of the Price field in the Price table.
In Mysql, we have several ways to do this. One is to update table1 t1, table2 ts:

The Code is as follows:	Copy code
UPDATE product p, productPrice pp SET pp. price = pp. price * 0.8 WHERE p. productId = pp. productId AND p. dateCreated <'2017-01-01'

Another method is to use inner join and then update:

The Code is as follows:	Copy code
UPDATE product p Inner join productPrice pp ON p. productId = pp. productId SET pp. price = pp. price * 0.8 WHERE p. dateCreated <'2017-01-01'

In addition, we can also use left outer join to update multiple tables. For example, if there is no Product price record in the ProductPrice table, set the isDeleted field of the Product table to 1, as shown in the following SQL statement:

The Code is as follows:	Copy code
UPDATE product p Left join productPrice pp ON p. productId = pp. productId SET p. deleted = 1 WHERE pp. productId IS null

In addition, in the above examples, the two tables are associated, but only the records in one table can be updated at the same time, as shown in the following SQL:

The Code is as follows:	Copy code
UPDATE product p Inner join productPrice pp ON p. productId = pp. productId SET pp. price = pp. price * 0.8, P. dateUpdate = CURDATE () WHERE p. dateCreated <'2017-01-01'

The two tables are joined to update the price field of the ProductPrice table and the dateUpdate field of the Product table.