Postgresql和更新

Question

我想询问有关SUM的信息，然后在Postgresql中更新它。

在我的例子中，我想把(单位)和在库存表中，然后用表产品(库存卷)进行扣除/减法。

我能够把库存表中的单位加在一起：

     SELECT NAME,SUM(UNITS) AS MINUS
     FROM STOCKDIARY
     INNER JOIN PRODUCTS
     ON PRODUCTS.ID = STOCKDIARY.PRODUCT
     GROUP BY NAME

     result =>
     name             ||    minus
     USB CABLE 1M     ||    -8  
     USB CHARGER      ||    -3  

那么问题是如何减去减去和的库存量，并将其更新回products表？

库存表

id(pk)，日期更新，产品，单位，价格

产品表

名称，id(pk)，价格，价格，库存量

@Vivek.S .谢谢，它起作用了。

    UPDATE products
    SET stockvolume = sub.minus
    FROM (SELECT
    SUM(UNITS) AS MINUS,PRODUCTS.ID
    FROM STOCKDIARY
    INNER JOIN PRODUCTS ON PRODUCTS.ID = STOCKDIARY.PRODUCT
    GROUP BY NAME) sub
    WHERE sub.productid = products.productid

Answer 1

 update  products  set stockvolume = stockvolume - diary.minus from 
(SELECT  STOCKDIARY.product,SUM(UNITS) AS MINUS
 FROM STOCKDIARY
 INNER JOIN PRODUCTS
 ON PRODUCTS.ID = STOCKDIARY.PRODUCT
 GROUP BY STOCKDIARY.product) as diary where products.id= diary.product

不要使用保留词作为表名、列名等.)