• notice
  • Congratulations on the launch of the Sought Tech site

Mysql cross-table update multi-table UpdateSQL statement summary

Suppose we have two tables, one table is the Product table to store product information, which has the product price column Price; the other table is the ProductPrice table, we need to update the price field Price in the ProductPrice table to the price field in the Price table 80%.
In Mysql, we have several methods to do this, one is to update table1 t1, table2 ts...:
The code is as follows:

UPDATE product p, productPrice pp
SET pp.price=pp.price * 0.8
WHERE p.productId=pp.productId
AND p.dateCreated < '2004-01-01'

Another method is to use inner join and then update:
The 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.dateCreated < '2004-01-01'

In addition, we can also use left outer join to update multiple tables, for example, if there is no product price in the ProductPrice table To record, set the isDeleted field of the Product table to 1, and the following SQL statement:
The code is as follows:

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 several examples are related to the two tables, but only update the records in one table, in fact, it is possible to update the two tables at the same time, as follows sql:
code like Down:

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'

Associate two tables and update The price field of the ProductPrice table and the dateUpdate of the Product table field are two fields.

Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+