mysql - 连接两个表时如何修复 FROM 不正确的语法错误？

Question

我正在开发一个数据库，我在这个问题中使用了 2 个特定的表，分别是 Artwork(ArtworkID、Price、PurchaseID)和 Purchase(PurchaseID、Total)

我想将具有相同 Artwork.PurchaseID(特别是 ID“D4758”，但我希望程序能够在同一查询中计算所有 PurchaseID)的艺术品的 Artwork.Price 相加。

然后我希望将加在一起的价格放入相应 Purchase.PurchaseID 行下的 Purchase.Total

我尝试了多种方法，但我一直在第 3 行收到 SQL 语法错误，我尝试将 Artwork 更改为 Purchase，但我仍然明白。我对 Joins 还很陌生，所以我不确定该怎么做。下面是我开发的代码:

UPDATE Purchase
SET Purchase.Total = SUM(Artwork.Price)
FROM Artwork
INNER JOIN Artwork ON Purchase.PurchaseID = Artwork.PurchaseID
WHERE Artwork.PurchaseID = 'D4758';

结果应将 Purchase.Total 从 NULL 更改为 Artwork.Price 的十进制 (5,2) SUM 值，其 PurchaseID 为“D4758”。

我反而得到了错误

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM Artwork INNER JOIN Artwork ON Purchase.PurchaseID = Artwork.PurchaseID WH' at line 3

Answer 1

已解决:

UPDATE Purchase
    SET Total = (SELECT sum(Price)
                 FROM Artwork
                 WHERE Purchase.PurchaseID = Artwork.PurchaseID
                )
 WHERE Purchase.PurchaseID = 'D4758'