IBM Mainframe Forum

Hi All,

I am having a table having 50,000 Records.I want to delete 1st 5000 Record from the table. How can I achieve this.

Is there any Function Equivalent to ROW_NUM() in DB2.

Thanks
Kind Rgds

what do you mean "first" ?
There is no implicit order in DB2; The whole concept of first and last is meaningless in a relational database.

Hi Guy C,
Meaning of "First" is Starting from Record Number 1 To Record Number 5000 to be Deleted from table. For Example a Table Having a Key field which is Auto generated having 50,000 Records, if I delete "First" 5000 Records, when I open / browse Table, it Should start from 5001 (which is 1st Record, after deleting 5000 records). I hope I am clear now.

Thanks.

Hello,

Repeating what GuyC posted - there is no such thing as the "first 5000 records". . .

If you have a "record number" column in the row (tables do not have records), you could delete rows where "record number" is from 1 to 5000.

Otherwise, this is not something to be done. Unless you use an ORDER BY some column(s) and delete the first 5000 returned.

Terminology is critical in IT, where similar terms may mean very differnt things. DB2 databases have "rows", they do not have "records". You can refer to the 5000th record of a file, for example, as there is an order imposed on the file. However, if you issue the same query to a database three times to retrieve 5000 rows, you might get back 3 different sets of 5000 rows -- or the same set of 5000 rows. You do not know what you will get back, though, unless you use an ORDER clause as part of the query.

So basicaly the very concept of the question you asked makes no sense as you asked it -- you could equally delete every 10th row of the database. Either reformulate the question to make sense, or give up on the idea of doing whatever you think you are accomplishing by asking for nonsense.

Hello,

It may help if you explain what business requirement will be met if you do this. Why does someone believe "the first 5000" rows should be deleted?

It is most likely that several of us have done what you need to do, but so far we do not know exactly what this is.

Possibly you could post 15 representative rows (showing only the columns that are needed for this) and explain which are the "first 5" and why these should be deleted.

I'm sure you are not asking for

and yes there is such a thing like row_num() why not open a db2 manual and look it up.