Deleting rows

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 donot 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. We are dealing with over a hundred thousand table2 rows to delete Any suggestions would be appreciated Thanks -- Gerard MacNeil M.F. Schurman Company, Limited gerardm@schurmans.com http//:www.schurmans.com

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 donot 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. You can take a page from my dbdelete.ec code which pretty much works this way and generate a delete statement with a large IN () clause and then execute the statement when you have collected enough keys to make it efficient. My testing shows that using rowids as the key I get best delete performance by generating and executing an IN() clause containing 2048 rowids. Art S. Kagel