where EXISTS / NOT EXISTS do not work (I am really pannic) 
Author Message
 where EXISTS / NOT EXISTS do not work (I am really pannic)

All,

This WHERE EXISTS () in this UPDATE statement does not work
UPDATE SERVER

SET active_flag = 'F'

WHERE EXISTS (SELECT 1 FROM DELETED_SERVER DS (NOLOCK)

WHERE DS.server_id_s = SERVER.server_id_s)

It sets every record in my SERVER table to 'F'.  When I change the statement
to

UPDATE SERVER

SET active_flag = 'F'

WHERE server_id_s  IN (SELECT server_id_s FROM DELETED_SERVER DS (NOLOCK))

Similarity, I have many cases where the NOT EXISTS does not work, but the
NOT IN work?

What is going on with SQL 2000?  Microsoft really makes me nervous here.  I
have tons of existing T-SQL code out there that use EXISTS/NOT EXISTS
clauses.  And I am talking about billions of dollars in transactions and
thousands of customers.

Really Nervous-guy.



Sun, 05 Mar 2006 19:37:37 GMT
 where EXISTS / NOT EXISTS do not work (I am really pannic)

Does this column allow nulls?  Are there any?



Quote:

> All,

> This WHERE EXISTS () in this UPDATE statement does not work
> UPDATE SERVER

> SET active_flag = 'F'

> WHERE EXISTS (SELECT 1 FROM DELETED_SERVER DS (NOLOCK)

> WHERE DS.server_id_s = SERVER.server_id_s)

> It sets every record in my SERVER table to 'F'.  When I change the
statement
> to

> UPDATE SERVER

> SET active_flag = 'F'

> WHERE server_id_s  IN (SELECT server_id_s FROM DELETED_SERVER DS (NOLOCK))

> Similarity, I have many cases where the NOT EXISTS does not work, but the
> NOT IN work?

> What is going on with SQL 2000?  Microsoft really makes me nervous here.
I
> have tons of existing T-SQL code out there that use EXISTS/NOT EXISTS
> clauses.  And I am talking about billions of dollars in transactions and
> thousands of customers.

> Really Nervous-guy.



Sun, 05 Mar 2006 20:14:25 GMT
 where EXISTS / NOT EXISTS do not work (I am really pannic)
It should work , see following example on northwind database.

update customers
set customerid = customerid
where exists
(select 1 from orders o (nolock) where o.customerid = customers.customerid)

--There are 89 matching rows in customers table with the matching customerid
of orders table.
--which can be confirmed by following query

select count(*) from customers where customerid in(select customerid from
orders)
--OR
select count(*) from customers where
exists
(select * from orders o where o.customerid = customers.customerid)

--
- Vishal


Quote:

> All,

> This WHERE EXISTS () in this UPDATE statement does not work
> UPDATE SERVER

> SET active_flag = 'F'

> WHERE EXISTS (SELECT 1 FROM DELETED_SERVER DS (NOLOCK)

> WHERE DS.server_id_s = SERVER.server_id_s)

> It sets every record in my SERVER table to 'F'.  When I change the
statement
> to

> UPDATE SERVER

> SET active_flag = 'F'

> WHERE server_id_s  IN (SELECT server_id_s FROM DELETED_SERVER DS (NOLOCK))

> Similarity, I have many cases where the NOT EXISTS does not work, but the
> NOT IN work?

> What is going on with SQL 2000?  Microsoft really makes me nervous here.
I
> have tons of existing T-SQL code out there that use EXISTS/NOT EXISTS
> clauses.  And I am talking about billions of dollars in transactions and
> thousands of customers.

> Really Nervous-guy.



Sun, 05 Mar 2006 20:21:34 GMT
 where EXISTS / NOT EXISTS do not work (I am really pannic)
You need to look up how [NOT] EXISTS() and IN() work.

 The IN() predicate is a shorthand for a list of OR-ed equality tests:

   x IN (a, b)

((x=a) OR (x=b))

Now let's make a subtle change:

   x IN (a, NULL)

((x=a) OR (x=NULL))

What do you know about the expression (x=NULL)?  It is a constant
UNKNOWN!  

 (TRUE OR UNKNOWN) = TRUE
 (FALSE OR UNKNOWN) = UNKNOWN

Not so bad, but if the IN() list is all NULLs, you will fail.  However,
NULLs exist, even if they have no known value, so:

 x IN (<<returns all NULLs>>) = UNKNOWN
 EXISTS (<<returns one or more NULLS>>) = TRUE

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sun, 05 Mar 2006 21:33:37 GMT
 where EXISTS / NOT EXISTS do not work (I am really pannic)
David,

  You seem to have left out part of a sentence after "When I change the
statement to
<query>

???  When you change it, what???

In any case, if the first update changes active_flag to 'F' for
every row of SERVER, it sounds like every server_id_s value
in SERVER is present in DELETED_SERVER.  Why do you
think this is not working?  It's possible that the problem is in
something you aren't showing, also.

The situation is different with NOT IN.  If the IN list of a
NOT IN query contains one or more NULLs, NOT IN (list with
null) will never be true, and no rows will be updated.

Steve Kass
Drew University

Quote:

>All,

>This WHERE EXISTS () in this UPDATE statement does not work
>UPDATE SERVER

>SET active_flag = 'F'

>WHERE EXISTS (SELECT 1 FROM DELETED_SERVER DS (NOLOCK)

>WHERE DS.server_id_s = SERVER.server_id_s)

>It sets every record in my SERVER table to 'F'.  When I change the statement
>to

>UPDATE SERVER

>SET active_flag = 'F'

>WHERE server_id_s  IN (SELECT server_id_s FROM DELETED_SERVER DS (NOLOCK))

>Similarity, I have many cases where the NOT EXISTS does not work, but the
>NOT IN work?

>What is going on with SQL 2000?  Microsoft really makes me nervous here.  I
>have tons of existing T-SQL code out there that use EXISTS/NOT EXISTS
>clauses.  And I am talking about billions of dollars in transactions and
>thousands of customers.

>Really Nervous-guy.



Sun, 05 Mar 2006 21:51:23 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. EXISTS/NOT EXISTS vs. IN/NOT IN

2. Failure does not work if source file does not exists

3. Not Exist Query not working

4. Opening a dBase file : file not found but it exists really

5. Exists and NOT exists

6. if exists/if not exists problem

7. EXISTS/NOT EXISTS

8. Exaple of Exists an Not Exists

9. If exist (proc name) does not work consistently

10. Attempting to branch on possible insert if row exists is not working

11. The remote server does not exist or has not been designated as a valid Publisher

12. Different Results from NOT IN versus NOT EXISTS?


 
Powered by phpBB® Forum Software