Re: delete from table based on value from another

First, MAX(ROWID)/10 MAY NOT give you the ROWID representing the end of the first 10% of rows, and certainly if you do this more than once it will not return the OLDEST rows. ROWIDs are NOT sequential integers (unless the table is fragmented WITH ROWIDS) and don't even exist for fragmented tables unless you add the WITH ROWIDS clause. For non-fragmented tables the rowid is the records ordinal page number within the table left shifted 8 bytes plus the slot number of the row on the page on which it resides. So, IFF all of the tables pages are completely full of rows, and the rows are not variable length so that different pages contain different numbers of rows, and the rows are not longer than a page you MAY actually delete approximately 10% of your data. However, when new data are added those new rows will first be placed on those emptied pages so the next time you run this cleanup you'll be deleting the most recent data! OK, that said, the problem with your delete statement is that you are trying to delete from one table where (<subquery>) that syntax isn't supported in anydatabase that I'm aware of. I think that you want: delete from sbjb_log where ROWID < (select idx from RIDX); Art S. Kagel ----- Original Message ----- From: James B. Fannin <ids@iiug.org> At: 6/14 11:24:45 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.