Re: Delete Cursor: What To Use In Select Clause?

I have seen a couple of answers, but neither of them addressed what I would regard as the fundamental question, which is: * If you aren't interested in the values from the SELECT, why are you using a SELECT FOR UPDATE and DELETE WHERE CURRENT OF statements? You should simply be using a single DELETE statement with a WHERE clause, and that should be exactly the same WHERE clause as in the SELECT statement. This has the additional advantage of being much faster as no data has to be passed back and forth between the program and the engine. One possible reason for using the cursor might be that you've run into problems with references to the table from which you are deleting data in sub-queries in the WHERE clause, or something along those lines. If so, you can still deal with it, though it takes two statements to remove the rows, and one to clean up: SELECT PrimaryKeyColumn FROM SomeTable WHERE ...Some Complex Condition... INTO TEMP T1; DELETE FROM SomeTable WHERE PrimaryKeyColumn IN (SELECT * FROM T1); DROP TABLE T1; If you have a multi-column primary key, it is messier still, but it can be done. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> >From: "Alexander J. Oss" <alex.oss@films.com> >Date: Wed, 03 Jul 1996 16:30:13 -0300 >X-Informix-List-Id: <news.25735> > >I am using a SELECT ... FOR UPDATE cursor to perform DELETE ... WHERE >CURRENT statements. > >Since I am not interested in the contents of the records I am deleting, I >don't really need anything in the select clause of the SELECT statement, >but it seems that syntax requires me to put something there. I'm >currently just using the serial not null primary key field. > >Does it matter what goes there? I know that with the NOT EXISTS >subquery, the manual says that you might as well use SELECT * "because >the existence of the whole row is tested; specific column values are not >tested" [Syntax v7.1 1-529]. (Is this true?)