Re: Slow Cursor

On Thu, 1 Oct 1998 15:08:21 -0500, rfurdzik@paulweiss.com wrote: > >I think you can do this all in one step (you not deleteing from second tabl= >e it=20 >is null)=2E > >Delete from FirstTable As A, OUTER SecondTable As B >Where A=2EPK1=3DB=2EPK2 >AND A=2EPK2=3DB=2EPK2 >AND B=2EPK2 IS Null I assume the above should be something like (with the last 2 changed to 1 on the first line of the where clause): Delete from FirstTable As A, OUTER SecondTable As B Where A.PK1=B.PK1 AND A.PK2=B.PK2 AND B.PK2 IS Null To the extent that I can even read what you write (please change your character set to 7 bit ASCII or ISO 8859-1) this statement will not work. Even in 7.3 you can't do joins in a delete statement. There have been some talk of implementing it (may be it is available in 9.x), but even then you can't test for NULL in any value in a field in the outer table. You make the common mistake of believing this test is done after the join. It is however done before the join, so only rows where the tested field of the B table is NULL in the database will be included. This means no rows whould have been deleted even if the syntax had been allowed. This also becomes quite obvious when you see that you expect B.PK2 both to be NULL and equal to A.PK2. Not good. How could the engine possibly know that you want it to do the join with A.PK2 = B.PK2 first and then test B.PK2 for NULL after the join? This is a common problem and that might warant some special syntax. The only current solution I know is to use a temporary table as suggested in my other posts. Nils Myklebust NM Data AS Norway E-mail: Nils.Myklebust@nmdata.com FAQ at: http://www.iiug.org/techinfo/faq/faq_top.html (Now with ODBC info under "Third party products".)