Summary of usage methods for MySQL UPDATE statements

MySQL UPDATE statement Base usage

Updating data in a database

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

Grammar

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

Cases

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 one more than the current value:

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

MySQL update assignment is evaluated from left to right. For example, the following statement doubles the age column and then increases it:

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

You can also perform an update operation that includes multiple tables. The table_references clause lists the tables that are included in the Union. Here 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 to implement multiple table updates

Suppose we have two tables, a table that holds product information for the product table, which contains price list prices; Another table is the Productprice table, and we want to update the Price field in the Productprice table to 80% of the price field in the prices table.
In MySQL we have several means to do this, one is update table1 t1, table2 ts ... The way:

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 < ' 2004-01-01 '

Another approach is to use the 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 < ' 2004-01-01 '

In addition, we can use the left outer join to do more table update, for example, if there is no product price record in the Productprice table, the isdeleted field of the product table is set to 1, 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, the above examples are related to two tables, but only update the records in one table, in fact, you can update two tables, 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 < ' 2004-01-01 '

Two tables are associated, updating the Price field of the Productprice table and the Dateupdate two fields of the Product table field