Emptying a table but only by deleting a specified number of rows at a time

[Kim Lilienfeld]

Hi

I've got the problem where I need to empty a table (containing approx 360 000 rows) but I am only allowed to delete 50 000 rows at a time. It is an informix database and the table has no incrementing id.

Any help would be appreciated.

Thanks.
Kim

[Paul Sturrock]

What, and it won't just let you do this?:

truncate table tableName;

[Henrique Sousa]

Hi Kim,

Maybe you could use some database-specific query using the rowid. I haven't used informix yet, but it would be something like

delete from your_table where rowid <= 50000

(of course, I assumed rowid starts with 1)

[Adeel Ansari]

this is the ROWID example of ORACLE.

ROWID
------------------
AAAL+ZAAEAAAAAdAAA
AAAL+ZAAEAAAAAdAAB
AAAL+ZAAEAAAAAdAAC
AAAL+ZAAEAAAAAdAAD
AAAL+ZAAEAAAAAdAAE
AAAL+ZAAEAAAAAdAAF
AAAL+ZAAEAAAAAdAAG
AAAL+ZAAEAAAAAdAAH
AAAL+ZAAEAAAAAdAAI
AAAL+ZAAEAAAAAdAAJ
AAAL+ZAAEAAAAAdAAK

[Kim Lilienfeld]

Thanks for all the help guys

Am gonna play around and see what works!

Thanks a million and 2..
K

[Shailesh Chandra]

delete from your_table where rowid <= 50000

(of course, I assumed rowid starts with 1)

I think in Oracle it should be like

delete from your_table where rownum < 50001
[ October 19, 2004: Message edited by: Shailesh Chandra ]

[Adeel Ansari]

Originally posted by Shailesh Chandra:

delete from your_table where rowid <= 50000

(of course, I assumed rowid starts with 1)<hr></blockquote>


I think in Oracle it should be like

delete from your_table where rownum < 50001
[/QB]

cent percent AGREEMENT