Update table using a select query

Question

So i have read a few posts on here but i cant seem to get this working on MySQL.

Pretty much I have a 'count' of records with an itemid that i want to update into my [items] table based on the itemid into [items].[popularity].

This is what i have tried:

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

Which returns a MySQL error:

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) AS CountOfScriptID, scri' at line 3

If i change this to a SELECT query it works fine, either selecting from [items] or the count query however the update statement is failing!

Any advice would be great, from what i have read I can't see where I'm going wrong with this.

Answer 1

The right way to do that, doing the join of tables before 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

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