Delete Records.



IBM's flagship relational database management system

Delete Records.

Postby Vineet » Mon Feb 07, 2011 7:01 pm

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
Vineet
 
Posts: 86
Joined: Tue Jun 19, 2007 11:38 am
Has thanked: 0 time
Been thanked: 0 time

Re: Delete Records.

Postby GuyC » Mon Feb 07, 2011 7:36 pm

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.
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Delete Records.

Postby Vineet » Sun Feb 13, 2011 6:55 am

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.
Vineet
 
Posts: 86
Joined: Tue Jun 19, 2007 11:38 am
Has thanked: 0 time
Been thanked: 0 time

Re: Delete Records.

Postby dick scherrer » Sun Feb 13, 2011 8:41 am

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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Delete Records.

Postby Robert Sample » Sun Feb 13, 2011 8:50 am

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.
Robert Sample
Global moderator
 
Posts: 3719
Joined: Sat Dec 19, 2009 8:32 pm
Location: Dubuque, Iowa, USA
Has thanked: 1 time
Been thanked: 279 times

Re: Delete Records.

Postby dick scherrer » Sun Feb 13, 2011 11:35 am

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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Delete Records.

Postby GuyC » Mon Feb 14, 2011 2:41 pm

I'm sure you are not asking for
delete from table where id < 5001
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Delete Records.

Postby GuyC » Mon Feb 14, 2011 2:43 pm

and yes there is such a thing like row_num() why not open a db2 manual and look it up.
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post