使用选择查询更新表 [英] Update table using a select query

问题描述

所以我在这里读了一些帖子，但是我似乎无法在MySQL上运行它.

我有一个记录"，其中有一个项目编号，我想根据该项目编号将其更新为我的[项目]表.[受欢迎程度].

这是我尝试过的:

 更新项目SET items.popularity = countitems.countofscriptiD来自项目内部联接(SELECT Count(scripts.ScriptID)AS CountOfScriptID，scripts.ItemIDFROM脚本GROUP BY scripts.ItemID)作为国家项目上items.itemid = countitems.itemid 

哪个返回MySQL错误:

1064-您的SQL语法有错误；检查与您的MariaDB服务器版本相对应的手册以使用正确的语法

'FROM项目附近INNER JOIN(SELECT Count(scripts.ScriptID)ASCountOfScriptID，在第3行抄写

如果我将其更改为SELECT查询，则可以正常工作，无论是从[items]中选择还是从count查询中进行选择，但更新语句均失败！

任何建议都是很棒的，从我读过的书中我看不出我在哪里出错了.

解决方案

做到这一点的正确方法，是在SET之前进行表的联接:

 更新项目内部联接(SELECT Count(scripts.ScriptID)AS CountOfScriptID，scripts.ItemIDFROM scripts GROUP BY scripts.ItemID) 作为 countitems上items.itemid = countitems.itemidSET items.popularity = countitems.countofscriptiD 

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

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.

解决方案

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

这篇关于使用选择查询更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！