Solved

How to delete first N rows in DB2 ?

Posted on 2004-10-11
5
2,203 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
  • 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DB2 what is copybook? 4 549
grant user read only access to DB2 V 8.02 10 1,006
OPENQUERY INSERT not recognizing linked server 12 562
Date and Time Conversion from Numeric Fields 21 277
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…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

772 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