Suppose we have two tables, one table for the product table, a product listing price, and another table for the Productprice table, which 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:
UPDATE product P, Productprice pp
SET pp.price = Pp.price * 0.8
WHERE P.productid = Pp.productid
and p.datecreated < ' 2004-01-01 '
Another way is to use inner join and then update:
Copy CodeThe code is as follows: UPDATE product P INNER JOIN productprice pp on p.productid = pp.productid SET pp.price = pp.price * 0.8 WHERE P.datec reated < ' 2004-01-01 '
Alternatively, we can use the left outer join to do a multi-table update, for example, if there is no product price record in the Productprice table, set the IsDeleted field of the product table to 1, the following SQL statement:
Copy CodeThe code is as follows: UPDATE product P left joins Productprice pp on p.productid = pp.productid SET p.deleted = 1 WHERE pp.productid is null
In addition, the above examples are related to the two tables, but only update the records in a table, in fact, you can update both tables, the following SQL:
Copy CodeThe code is as follows: 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 '