Problem with an update query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with an update query

Author

Topic

CSharpNewbie
Starting Member

39 Posts

Posted - 2011-02-04 : 08:28:50

Hi, when I run the following query, I get all the records updated (which is not what I want). I want to update only the records that satisfy the internal select query. Any ideas how to resolve this?

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)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-04 : 08:55:03

Try this

Update t
Set t.SeniorManagerFlag = 'Y'
from tblCommunityServiceMembers as t inner join dbo.tblEmployee as emp
on charindex(emp.EmployeeID, csm.CommunityServiceMembers,1) > 0

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-02-04 : 08:55:37

This query will have horrible scalability but here's how you do it, provided the info given

Update csm
  Set SeniorManagerFlag = 'Y'
from dbo.tblEmployee emp
  inner join dbo.CommunityServiceMembers csm
    on charindex(emp.EmployeeID, csm.CommunityServiceMembers,1) > 0

- Lumbago
My blog-> www.thefirstsql.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-04 : 09:02:10

[code]UPDATE csm
SET csm.SeniorManagerFlag = 'Y'
FROM dbo.tblEmployee AS e
INNER JOIN dbo.tblCommunityServiceMembers AS csm ON csm.CommunityServiceMembers LIKE '%' + emp.EmployeeID + '%'[/code]
Please note that when you use EXISTS you must have a reference to the outer query.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-04 : 09:04:19

[code]UPDATE csm
SET csm.SeniorManagerFlag = 'Y'
FROM dbo.tblCommunityServiceMembers AS csm
WHERE EXISTS (SELECT * FROM dbo.tblEmployee AS e WHERE CHARINDEX(emp.EmployeeID, csm.CommunityServiceMembers, 1) > 0)[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources