sql-update - 使用选择查询更新表

Question

所以我在这里读了一些帖子，但是我似乎无法在MySQL上正常工作。
我几乎有一个带有itemid的“计数”记录，我想根据itemid转换为[items]。[popularity]来更新到我的[items]表中。
这是我尝试过的:

Update items
SET items.popularity = countitems.countofscriptiD
FROM items
INNER JOIN
(SELECT Count(scripts.ScriptID) AS CountOfScriptID, scripts.ItemID
FROM scripts GROUP BY scripts.ItemID) as countitems
ON
items.itemid =  countitems.itemid

返回MySQL错误:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use

near 'FROM items INNER JOIN (SELECT Count(scripts.ScriptID) ASCountOfScriptID, scri' at line 3

如果我将其更改为SELECT查询，则可以正常运行，无论是从[items]中选择还是从count查询中进行选择，但是update语句失败!
任何建议都是很好的，根据我的阅读，我看不出我在哪里出错了。

Answer 1

正确的方法是，在SET之前进行表的联接:

UPDATE items
INNER JOIN
(SELECT Count(scripts.ScriptID) AS CountOfScriptID, scripts.ItemID
FROM scripts GROUP BY scripts.ItemID) as countitems
ON
items.itemid =  countitems.itemid
SET items.popularity = countitems.countofscriptiD

参见 https://dev.mysql.com/doc/refman/8.0/en/update.html