Solved

How to delete first N rows in DB2 ?

Posted on 2004-10-11
5
2,317 Views
Last Modified: 2012-06-21
Hi, I am trying to delete first N rows in a DB2 command,
however I can't/don't want to use:
"DELETE FROM TABLE_NAME WHERE COL_NAME IN (SELECT COL_NAME FROM TABLE_NAME FETCH FIRST 'N' ROWS ONLY)"

I have a dynamic build of the delete statement and I have what I have is a constant delete statement that I can add things before it, in the middle of it or after it but I really don't want to change the base delete.

Thanks,
   AmS.
0
Comment
Question by:ams61
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12277816
You didn't explain why you don't want to use a query with a sub-select, so I'm not sure what you're looking for, but one option is to create a bit field with a default of zero, set it to 1 when you want it deleted, then run a DELETE statement with a WHERE clause that limits the deletion to records where the value is 1.
0
 

Author Comment

by:ams61
ID: 12282155
Thanks,
I will explain... I have a constant delete sql statement that I can add parts to its beginning, and\or to its end, I can also add constant statements into the delete statement but it will be very dificult to me to change the original delete statement, especially if I don't know from which table I am going to delete (the delete statement is built dynamicly).
0
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 250 total points
ID: 12286867
Is
>I have a constant delete sql statement that I can add parts to
just another way of saying that you build the statement dynamically?

If so, I don't see why you couldn't add
   WHERE COL_NAME IN (SELECT COL_NAME FROM TABLE_NAME FETCH FIRST 'N' ROWS ONLY)
as needed.

Also, if you're building the delete statement dynamically, I don't understand what you mean about not being able to "change the original delete statement".
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
DB2 CONCAT FAILING 4 124
The Event Monitor "DB2DETAILDEADLOCK" has reached its file 7 433
Data transfer to iSeries failing with message ID SQL0462 6 204
Monitor SQL Insert 8 120
November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question