October 20, 2008 at 12:12 pm
Hello,
I would really appreciate some help with this simple statement. I am trying to set the account status of certain users (250 of them) to be disabled if they exist in a column in another table (same DB) and it is instead effecting every record (3000 of them) in the table.
Here's my script:
UPDATE User
SET AccountStatus = 'Disabled'
WHERE EXISTS
(SELECT * FROM User a
INNER JOIN sheet1$ b
ON a.emailaddress = b.Emailaddress)
There are only 250 users in the sheet1$ table. It's like my WHERE EXISTS is completely ignored and it updates every record in the User table rather than just the 250 that exist in the sheet1$ table.
If I just execute the following query, It returns the correct amount of records so I know i'm just not using the WHERE EXISTS clause correctly:
SELECT * from User a
INNER JOIN sheet1$ b
ON a.emailaddress = b.Emailaddress
Thanks in advance,
Perry
October 20, 2008 at 12:33 pm
UPDATE a
SET AccountStatus = 'Disabled'
FROM User a
INNER JOIN sheet1$ b
ON a.emailaddress = b.Emailaddress
INNER JOIN already filtering your result records.
October 20, 2008 at 12:34 pm
or
do the proprietary update:
UPDATE a
SET AccountStatus = 'Enabled'
FROM User a
INNER JOIN sheet1$ b
ON a.emailaddress = b.Emailaddress
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
October 20, 2008 at 1:22 pm
Ah, I see! Thank you both very much!
October 20, 2008 at 2:28 pm
UPDATE User
SET AccountStatus = 'Disabled'
WHERE EXISTS
(SELECT * FROM User a
INNER JOIN sheet1$ b
ON a.emailaddress = b.Emailaddress)
I would also like to address the mis-use of EXISTS.
SELECT * FROM User a
INNER JOIN sheet1$ b
ON a.emailaddress = b.Emailaddress
will ALWAYS return the same thing. so if you
UPDATE User
SET AccountStatus = 'Disabled'
WHERE EXISTS (subquery)
it will ALWAYS do the same thing to ALL rows
EXISTS only looks to see if anything-what-so-ever is returned, then it will be true; if nothing is returned at all, then it will be false.
Just wanted to make sure you understood that and not just getting your query fixed.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply