Search code examples
sql-updatemariadb

Update MariaDB table using a select query

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.

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.

Solution

  • 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