Hi All,
I've an update statement which includes a sub-query. On top of this I
use *not in* clause. Well, when the update statement is run it goes on
forever.
Here is the SQL statement:
--------------------BGN----------------------
-- Should update around 4200 rows
UPDATE T1
SET T1.C01 = '12/31/2008'
WHERE T1.C01 IS NULL
AND T1.C02 = 333
AND T1.C03 NOT IN
-- Returns around 4000 rows
(SELECT convert(varchar,T2.C01)
FROM T2
WHERE T2.C02 = '01/01/2003'
AND T2.C03 IS NULL)
--------------------END----------------------

As described int the above SQL the number of rows returned by inner
query is around 4000 and the outer update statement should update
around 4200 rows. PLUS I'm forced to use "convert" function as the
fields the exist on either side of *not in* are odifferent type.

I've tried using Indexes in the sub-query,but it's of not much use.

Any ideas?

Cheers,
Gurram

yrg01@yahoo.com (Vamshi) writes:

>I've an update statement which includes a sub-query. On top of this I
>use *not in* clause. Well, when the update statement is run it goes
>on forever.

Try it with 'set rowcount 1' and see if even that takes forever. Try
'set showplan on' to see what it is doing. If you remove the 'not in'
subquery does your update go faster?

--
Ed Avis <ed@membled.com>