Update TableA with values from TableB?

If I have 2 tables, each have a product_stat DECIMAL and product_id INT column

I want to run a query that will append the product_stat from TableA to TableB on product_id. Then truncate TableA

Basically I am collecting data and temporarily storing it in TableA, and once a day I 开发者_Go百科want to move the data to TableB. So that TableB only has the data shifted once a day.

The quich solution is to use a subquery

UPDATE tableB SET product_stat = (
    SELECT product_stat FROM tableA
    WHERE tableB.product_id = tableA.product_id
)

But you can use UPDATE in conjunction with JOIN, which will have a better performance

UPDATE tableB
    INNER JOIN tableA ON tableB.product_id = tableA.product_id
SET tableB.product_stat = tableA.product_stat

UPDATE Authors AS A, Books AS B SET AuthorLastName = 'Wats' WHERE B.AuthID = A.AuthID AND   AND ArticleTitle='Something';