Mysql cross-Table update multi-Table update SQL statement Summary

Source: Internet
Author: User

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: Copy codeThe Code is as follows: 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: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. 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:Copy codeThe 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, 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: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 <'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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.