Re: Deleting rows

On Tue, 9 Feb 1999, gerard wrote: > I want to detete rows from table2 based on a date on table1 > ie delete from table2 > where table1.date < adate > and table2.fld1 = table1.fld1 > > I do not want to delete rows from table1. > > I have used a cursor to return rowid's of rows to delete from table2 and > then > delete the rows using the table2 rowid. > select table2.rowid > from table1, table2 > where table1.date < adate > and table2.fld1 = table1.fld1 > foreach rowid returned to tprowid delete from table2 > where table2.rowid = tprowid> > Is this the most efficient way to do this or is there a variation of the > first example > that is better or does someone have a better suggestion. This is slow because of all the data that has to be sent back and forth between the client and the server. What about: DELETE FROM Table2 WHERE fld1 IN (SELECT T1.fld1 FROM Table1 T1 WHERE T1.date < adate); This doesn't send much data back and forth. It is untested, but should be OK. Because the sub-query is not correlated, it should be reasonably quick. Yours, Jonathan Leffler (jleffler@informix.com) #include <wish/I/was/skiing.h> Guardian of DBD::Informix v0.60 (v0.61_02) -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn