December 16, 2010 at 9:31 am
I have the following query which is supposed to update the password and email address of users who are not in the 1000 and 1011 group.
update a set a.passwordHash =
'ca6588e891b19510c8a6d3d8fabd17ec1d54d201db23292250a69a36f3fca452',
a.creationDate = 1271954403664 , a.email = a.email + '.test'
from user a inner join groupUser b
on a.userID = b.userID
where b.groupID not in ('1000', '1011',)
However, I find that some users( who belong to the groups 1000 and 1011) also belong to groups i.e, 1001, 1016 etc and they are getting their profiles updated.
What I'm looking for is that users who are in at leasr the 1000 or 1011, do not get their profiles updated.
Thanks.
December 16, 2010 at 9:48 am
This should do the trick:
UPDATE a
SET a.passwordHash = 'ca6588e891b19510c8a6d3d8fabd17ec1d54d201db23292250a69a36f3fca452',
a.creationDate = 1271954403664,
a.email = a.email + '.test'
FROM a
WHERE NOT EXISTS (
SELECT 1
FROM u
INNER JOIN groupUser b
ON u.userID = b.userID
WHERE b.groupID IN ('1000', '1011')
AND a.userID = u.userId
)
-- Gianluca Sartori
December 16, 2010 at 10:33 am
Thanks; that worked!
December 16, 2010 at 10:47 am
what do you expect to be your third value in your in-list ?
where b.groupID not in ('1000', '1011',)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 16, 2010 at 1:02 pm
That was a typo; thanks for catching that!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply