How to delete the first 100 records from a table in Informix?

[mm8294]

I am an Oracle DBA and I am new to Informix. Now I need to delete some records (for example, the first 100 records)from a table in Informix for test purpose. Could somebody tell me how to do it? The query in Oracle database should be:
delete from test_table where rownum<101;

But it does not work in Informix.

Thanks you so much.

 

[olded]

Hi:

Informix has a SELECT FIRST number of rows syntax. This gets the first 2 two rowids from table test_tab. Unfortunately, you can't use it in a subselect (See Informix error -944) This precludes you from selecting into a temp table or deleting using a subselect.

In the past, I've written a shell sctip to select what I've wanted to delete; placed the data in a disk file, read the file and perform the delete.

Here's an example. I know this stinks, but .....

Regards,


Ed


#!/bin/ksh

dbaccess testdb << MSG
output to temp.tab without headings
select FIRST 2 rowid FROM test_tab;
MSG
# get rid blank lines and spaces
sed '/^$/d
s/ //g' temp.tab > newtemp.tab
while read i
do
dbaccess testdb << MSG
delete from test_tab where rowid = &quot;$i&quot;
MSG
done < newtemp.tab

 

[mm8294]

This is very helpful. Thank you so much.