CODEWITHSUNDEEP
databaseinformix
v0001
v0001

Reputation: 11

DB query for deleting first 100 rows of a table in IBM Informix

I tried with the following query but it didn't work.

delete from tableName where id IN (select FIRST 100 id from tableName)

Thanks in advance.

Upvotes: 1

Views: 3195

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 753675

The sub-query doesn't return a column. You'd need to use:

DELETE FROM tableName
 WHERE id IN (SELECT FIRST 100 id FROM tableName)

The query above generates error -944 (Cannot use "first", "limit" or "skip" in this context — an irritating restriction), so we have to work around the server:

$ sqlcmd -d stores -e 'create table tablename (id integer not null primary key)'
$ random -n 350 | sort -u | sqlreload -d stores -t tablename
348 rows committed
$ sqlcmd -e 'select count(*) from tablename'
348
$ sqlcmd -d stores \
>        -e 'select first 100 id from tablename into temp delete_me' \
>        -e 'delete from tablename where id in (select * from delete_me)'
$ sqlcmd -e 'select count(*) from tablename'
248
$

In the example, I don't need to drop the temporary table, delete_me, but in a program, it would probably be a good idea to do so explicitly once you're finished. It might be better to choose a less guessable name — a name based on a UUID or GUID or something like that might work instead.

Note that 'FIRST 100' is an indeterminate list of numbers; it could be almost any 100 rows from the table.

This sqlcmd is the original program of that name (or, at least, more original than Microsoft's johnny-come-lately of the same name, at any rate), which I wrote over a period of more than 25 years, and the source for it can be downloaded from the IIUG — International Informix Users Group.

Upvotes: 2

Michał Niklas
Michał Niklas

Reputation: 54302

Your query looks ok. If select FIRST 100 id from tableName returns something then such DELETE should delete those rows. Check what such SELECT returns.

Maybe you use it in transaction and then you do not commit those changes.

If you call such DELETE from smart SQL editor it should show you something like 100 rows changed. Such information (numbers of rows affected) is returned by executeUpdate() from Java JDBC or similar function in other environments.

Upvotes: 0

Related Questions