How to delete first X records using IBM i SQL

How to delete first few rows from a file with IBM i SQL

This morning I was working with a flatfile import from IFS into a DB2 database file. This should have been straight forward but the flat file contained several rows of crappy data at the start of the file. *ugh*

Since I am importing this data in a CLLE program, I quickly googled “how to delete table rows by rrn” only to go down a rabbit hole of different SQL versions, rowid’s and other complications caused by IBM i SQL being slightly different from all other SQL versions.

I did find a neat solution simply using the IBM i SQL RRN() function but then I found the LIMIT option… which is even easier. I like easy stuff.

I will detail both for your delight and pleasure ;).

So, for this blog let’s create a very easy file – with some duff data that I want to delete:

EASY SOLUTION #1

Finally, found an easy solution with the LIMIT option

I want to delete the first 3 rows in this file so I can just do:

delete from 'filename' limit 3

Yes, it’s really that simple.

NEAT SOLUTION #2

Assuming we are starting with a file that does not contain an RRN type field, with a unique row number in it, we can use SQL’s Relative Record Number function (RRN) to address this.

The same example with my BOB file would look like this:

select rrn(bob), bob from bob

The RRN function will show us the RRN of each row in the file:

So, to delete the first 3 rows I might type:

delete from bob where rrn(bob) <= 3
3 rows deleted from BOB in QTEMP.

Easy peasy lemon squesy