SQL更新查询返回错误

I am getting an error when running an UPDATE statement:

我在运行UPDATE语句时遇到错误:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

当未使用EXISTS引入子查询时,只能在选择列表中指定一个表达式

when I attempt to execute this SQL query:

当我尝试执行此SQL查询时:

Update tblCommunityServiceMembers  
Set SeniorManagerFlag = 'Y'   
Where EmployeeList =   
         (select distinct emp.EmployeeID, csm.CommunityServiceMembers,  
          csm.SeniorManagerFlag from dbo.tblEmployee emp,   
          dbo.CommunityServiceMembers csm  
          where charindex(emp.EmployeeID, > csm.CommunityServiceMembers,1) > 0)

The nested select query does return more than one record. So I want to update the flag value to Y for all those records. Any help is appreciated.

嵌套的选择查询确实返回多个记录。所以我想将所有这些记录的标志值更新为Y.任何帮助表示赞赏。

3 个解决方案

#1

If you want to update the flag for all records for which the inner query is true, then you can try following query. But still I dont understand the use of second parameter in CHARINDEX function. may be you need to correct it first.

如果要更新内部查询为true的所有记录的标志,则可以尝试以下查询。但我还是不明白在CHARINDEX函数中使用第二个参数。可能你需要先纠正它。

Update tblCommunityServiceMembers
Set SeniorManagerFlag = 'Y'
Where exists (select distinct emp.EmployeeID, csm.CommunityServiceMembers,
csm.SeniorManagerFlag from dbo.tblEmployee emp,
dbo.CommunityServiceMembers csm
where charindex(emp.EmployeeID, > csm.CommunityServiceMembers,1) > 0)

#2

The subquery is returning 3 fields. (employeeid, communityservicemembers, seniormanagementflag). You're returning it into an equality test, and SQL Server isn't telepathic enough to know which field you want to compare the EmployeeList against.

子查询返回3个字段。 (employeeid,communityservicemembers,seniormanagementflag)。您将它返回到相等测试,并且SQL Server不足以知道要将EmployeeList与哪个字段进行比较。

#3

An "equal to" matches the specified value with the selected value. Here as it appears, you want to just check that if the sub-query returns any value you need to update the table. So there's no point in checking for the equal values you just need an exists check for the same and also your select return 3 results where as there's a single column to check for,which will definitely throw an error.

“等于”将指定值与所选值匹配。在这里看来,您只想检查子查询是否返回更新表所需的任何值。所以检查相等的值是没有意义的,你只需要对它进行存在检查,并且你的select返回3结果,因为有一个要检查的列,这肯定会引发错误。

Hope this might help

希望这可能有所帮助

#1