Microsoft KB Archive/70849

PRSQL9103029: Certain Subqueries Will Give Unexpected Results

PSS ID Number: Q70849 Article last modified on 12-18-1992

1.10 1.11 4.20 OS/2

Summary: PROBLEM ID: PRSQL9103029

SYMPTOMS The following query does not return expected results if the subquery evaluates to 0 (zero): delete from tab1 where 1> (select count(*) from tab2 where col2 = tab1.col1)

CAUSE The subquery will evaluate to the value null instead of the value zero.

WORKAROUND To perform this functionality, use the following query: delete tab1 where col1 not in (select b.col1 from tab1 B where 0 > (select count(*) from tab2 where col2=b.co1))

STATUS Microsoft has confirmed this to be a problem in SQL Server versions 1.1, 1.11, and 4.2. We are researching this problem and will post new information here as it becomes available.

= Additional reference words: Transact-SQL =

Copyright Microsoft Corporation 1992.