delete from table based on value from another tab

Running IDS 7.32 - I am trying to delete rows from a log table based on a percentage of the size of the table. I have constructed the following SQLs. Everything works except the delete. I can't seem to find a way to pass the threshold number to the delete statement. Help please. drop table RIDX; create temp table RIDX (idx integer); insert into RIDX select (max(rowid)/10) from sbjb_log;-- this select statement is just to see how many rows are going to be deleted select count(*) from sbjb_log log , RIDX where log.rowid < RIDX.idx; delete from sbjb_log where (select * from sbjb_log, RIDX where sbjb_log.rowid < RIDX.idx) I have tried several different syntax for the delete statement and all give an error. I even tried putting the RIDX.IDX number calculation in a stored procedure and returning the value. However, I can't seem to find or figure out how to get that value into the delete statement. Any ideas or help would be appreciated. Send e-mail reply to jim_fannin@aotx.uscourts.gov

Use where exists if you're doing this sort of operation. delete from tab1 where exists (select 0 from tab2 where tab1.key=tab2.key); But if you are deleting 10% of the table, you might consider a stored procedure instead - then you can stash the value into a variable. Furthermore, rowid is deprecated, use a serial key or a sequence, or if it's a log table - then how about logdatetime? delete from log where logdatetime < current year to second - 7 units day; j. >From: "JAMES B. FANNIN" <jimf@isitfriday.com> >Date: 2007/06/14 Thu AM 10:24:13 CDT >To: ids@iiug.org >Subject: delete from table based on value from another tab [9355] >Running IDS 7.32 - I am trying to delete rows from a log table based on a >percentage of the size of the table. I have constructed the following SQLs. >Everything works except the delete. I can't seem to find a way to pass the >threshold number to the delete statement. Help please. > >drop table RIDX; >create temp table RIDX (idx integer); >insert into RIDX select (max(rowid)/10) from sbjb_log;>-- this select statement is just to see how many rows are going to be deleted >select count(*) from sbjb_log log , RIDX where log.rowid < RIDX.idx;> >delete from sbjb_log where >(select * from sbjb_log, RIDX where sbjb_log.rowid < RIDX.idx)> >I have tried several different syntax for the delete statement and all give an >error. I even tried putting the RIDX.IDX number calculation in a stored >procedure and returning the value. However, I can't seem to find or figure out >how to get that value into the delete statement. > >Any ideas or help would be appreciated. Send e-mail reply to >jim_fannin@aotx.uscourts.gov > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.