SQL update query returning an error

Question

I am getting an error when running an UPDATE statement:

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

when I attempt to execute this SQL query:

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.

1. What data type is EmployeeList? 2. What does > mean in charindex(emp.EmployeeID, > csm.CommunityServiceMembers,1)? I first thought it was a typing error, but then I'm not pretending to know all about CHARINDEX usage. 3. And just to clarify it, what type is emp.EmployeeID if it's not int? — Andriy M
– Andriy M, Commented Feb 4, 2011 at 7:01

Anil Soman · Accepted Answer · 2011-02-04 05:11:54Z

2

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.

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)

answered Feb 4, 2011 at 5:11

Anil Soman

2,4677 gold badges41 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marc B · Accepted Answer · 2011-02-04 00:59:42Z

1

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.

answered Feb 4, 2011 at 0:59

Marc B

362k44 gold badges433 silver badges508 bronze badges

4 Comments

Troy Over a year ago

well, I was intending to "update the seniormanagerflag for all records returned". What would be the best way to do this?

Marc B Over a year ago

return only the employeeIDs from the subquery. That's all your really need.for the outer query to work on.

Troy Over a year ago

Just curious, would the join work (in the nested select) if I only returned the employeeID ?

Marc B Over a year ago

Sure, joins don't require you to "select" the fields you're joining on. You can select field A, join on B&C, and never ever see the contents of B&C returned.

Tushar · Accepted Answer · 2011-02-04 06:21:31Z

0

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.

Hope this might help

answered Feb 4, 2011 at 6:21

Tushar

214 bronze badges

Collectives™ on Stack Overflow

SQL update query returning an error

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related