Deleting top rows
Amit Dixit asked how to delete the top n rows without a usable column, possibly via rowid. Art Kagel said rowids are not sequential unless fragmented WITH ROWID, so they do not reflect insert order, and the schema needs work. Andrew Lennard suggested adding a SERIAL column for FIFO deletes; Jean Sagi suggested a stored procedure with a FOREACH cursor.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi, How can i delete top n number of rows i dont have nay column which i can use in where clause to delete top rows. does Informix support delete based on rowid can anyone tell me exact sql statement for that. Thanks Amit.
Yes it does, however, how will you select rowids to delete if you have no combination of columns you can use to identify which rows to delete? The rowids are NOT sequential unless the table is fragmented WITH ROWID (in which case the rowid is actually a hidden SERIAL type column) otherwise the rowid is just the logical address of the row in the tablespace and if there have been deletes or multiple sessions adding rows, may have nothing at all to do with the order in which rows were added. Indeed your cleanup scheme will practically guarantee that many of the most recent rows added to the table will have the lowest valued rowids. Sounds like your schema needs work not your cleanup procedures. Art S. Kagel ----- Original Message ----- From: Amit Dixit <amdixit_x@hssworld.com> At: 11/ 8 10:45 > Hi, > How can i delete top n number of rows i dont have nay column which i > can use in where clause to delete top rows. > > does Informix support delete based on rowid can anyone tell me exact > sql statement for that. > > Thanks > Amit.
Is this related to your previous posting where you were asking how to delete 10,000 rows when you want to make space to insert 100,000 new ones? You were suggesting that you wanted to delete the 'earliest ones'. If you can't tell which are the earliest, because you don't have a column that holds that information, then I don't know how you'll decide! If you're intending to implement some sort of FIFO, then you could add a SERIAL column to your table and use that to determine which rows were inserted into the table first. >From: "AMIT DIXIT" <amdixit_x@hssworld.com> >To: ids@iiug.org >Subject: Deleting top rows [3645] Date: Mon, 8 Nov 2004 10:35:13 -0500 >(EST) > >Hi, > How can i delete top n number of rows i dont have nay column which i > can use in where clause to delete top rows. > > does Informix support delete based on rowid can anyone tell me exact > sql statement for that. > > Thanks > Amit. > > _________________________________________________________________ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger
You will need to define what top n means for you. After that you could implement a stored procedure with parameter n and use a foreach cursor in it to delete only n rows of data. Chucho! -----Original Message----- From: "AMIT DIXIT" <amdixit_x@hssworld.com> To: ids@iiug.org Date: Mon, 8 Nov 2004 10:35:13 -0500 (EST) Subject: Deleting top rows [3645] Hi, How can i delete top n number of rows i dont have nay column which i can use in where clause to delete top rows. does Informix support delete based on rowid can anyone tell me exact sql statement for that. Thanks Amit. Jean Sagi jeansagi@myrealbox.com jeansagi@yahoo.com