请帮我解决这个查询 [英] Please help me to solve this query

问题描述

嗨
我收到了这样的错误:此子查询最多可以返回一条记录"

请帮助我解决此查询

Hi
I got the error like "at most one record can be returned by this subquery"

Please help me to solve this query

UPDATE UserMenu SET UserMenu.MenuOrderNo = MenuOrderNo-1 WHERE ((([MenuOrderNo])=(select MenuOrderNo from UserMenu where(((MenuID) =7) AND ((UserId)='1'))+1)) AND ((UserMenu.UserId)='1'))

在此先感谢


Sheethal

thanks in advance


Sheethal

推荐答案

(select MenuOrderNo from UserMenu where(((MenuID) =7) AND ((UserId)=''1''))+1)) AND ((UserMenu.UserId)=''1''))子查询返回了多行.如果这是正常的，并且您的逻辑允许，请使用IN而不是=，如下所示:

The (select MenuOrderNo from UserMenu where(((MenuID) =7) AND ((UserId)=''1''))+1)) AND ((UserMenu.UserId)=''1'')) subquery returned more than one row. If this is normal, and your logic allows it, use IN instead of = before it, like this:

UPDATE UserMenu SET UserMenu.MenuOrderNo = MenuOrderNo-1 WHERE ((([MenuOrderNo]) IN (select MenuOrderNo from UserMenu where(((MenuID) =7) AND ((UserId)='1'))+1)) AND ((UserMenu.UserId)='1'))

问题出在您的比较中.您对MenuOrderNo使用相等的比较，因此选择必须精确地获取0或1条记录.现在，它返回多个记录.

根据您的逻辑，您需要更改比较或选择.如果可以更新几行，则应使用IN运算符，例如:

The problem is in your comparison. You use equal comparison for MenuOrderNo so the select must fetch exactly 0 or 1 records. Now it returns more than one record.

Depending on your logic you either need to change the comparison or the select. If it''s acceptable to update several rows then you should use IN operator, for example:

UPDATE UserMenu SET UserMenu.MenuOrderNo = MenuOrderNo-1 WHERE ((([MenuOrderNo]) IN (select MenuOrderNo from UserMenu where(((MenuID) =7) AND ((UserId)='1'))+1)) AND ((UserMenu.UserId)='1'))

如果您需要更新一条记录，请遍历SELECT语句以查看为什么它返回多行.

If you need to update a single record, then go through the SELECT statement to see why it is returning multiple rows.

这篇关于请帮我解决这个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！