View previous topic :: View next topic
|
Author |
Message |
RAJA-RAJA
New User
Joined: 11 Sep 2009 Posts: 10 Location: chennai
|
|
|
|
Can anyone pls let me know to delete the 'n' number of rows from a table ?
Already i tried with the query:
Delete from table where name in (select name from table where name = 's ' order by fetch first 10 rows only);
The above query is deleting all the records selected not only 10 records.
Can you suggest any other to do this?
Thanks for your help |
|
Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007 Posts: 792 Location: Chennai, India
|
|
|
|
Quote: |
Delete from table where name in (select name from table where name = 's ' order by fetch first 10 rows only); |
Is the query correct? It will return syntax error.
And, how you do run that query? using user-written program or any other? |
|
Back to top |
|
|
RAJA-RAJA
New User
Joined: 11 Sep 2009 Posts: 10 Location: chennai
|
|
|
|
I have given the model query which i ran in QMF and its deleting all the selected rows , not only 10 rows. |
|
Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006 Posts: 1353 Location: Israel
|
|
|
|
Then you should provide the criteria that will uniquely identify the 10 rows you want to delete! |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Your "requirement" seems to be rather "loose". . .
And as written, it cannot work.
What business function will be supported be deleting any 10 rows? As described, there would be no control of which rows were deleted even if this would work. . .
If you clearly explin what you are trying to accomplish (rather than how you tried to code it), someone may have a suggestion. |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
Which version of DB2 are you at? Can you use ROW_NUMBER()?
And you're missing the column-name in "order by", so yes, as shown the SQL should not work. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
Code: |
Delete from table
where UniqueKeyColumns in (select UniqueKeyColumns from table where name = 's ' fetch first 10 rows only); |
|
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
Guy - dare I ask, but should not one use ORDER BY with "fetch first n rows", though the original post does not really specify about the "first 10" or "last 10" from the "result-set"? Please assist. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
I thought so too, but apparantly an order by is not necessary.
Of course then it is just random 10 rows. You can add an order by (it could even be wise to do so on the clustering index)
the idea of deleting in "batches" is that you can do this in a loop with a commit, thus avoiding locking problems.
If the where-clause is too complicated and/or a temp result set must be built , then this could become costly. |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
Thanks GuyC. (Guy? What do you prefer? )
As you talked about "batch usage", the point make sense, however, my first perception about this thread was that there is a(n) STP on z/OS, request is origniated from a JAVA-screen and then ORDER BY might become a necessity, no? (ofsourse, much depend on the application).
Lately, I asked about ROW_NUMBER() so that this can be used:
Code: |
DELETE from table A WHERE (A.name) in
(SELECT T.name FROM
(SELECT ROW_NUMBER() OVER (ORDER BY name) AS ROWNUM, name FROM table) AS T
WHERE ROWNUM < 11); |
I've not checked it but I believe - with 10-rows on stake it won't be expensive enough too, yeah?
PS. Taking advantage of having a friendly DBA around here... . |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
you can indeed use row_number() to do the same. but the cost remains the same: if a temp resultset/ordering has to happen before DB2 can determine the rownumber / first 10 rows it will cost a lot for each set of 10 rows.
My first name is Guy , but because in english Guy is a synonym for dude, man, on forums I prefer GuyC so I know it's about me and not just any person.
I used the word "batch" as in group (of rows), not as a type of processing.
For mainframe jobs we have other and better coding techniques to manipulate commitfrequency/restartability.
We've used this principle in Java (coding native SQL, not calls to stp) because they also need to delete sometimes and they caused timeouts and lock-escalations. this way we ensure timely commits and communication between MF and servers keeping the thread open.
Ofcourse I would recommend a number higher than 10. closer to 10000 (depending on lock-granularity and so on). |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
Thank you GuyC - this helps. Have a good one. And
Quote: |
on forums I prefer GuyC |
That's pretty logical, GuyC...
Regards, |
|
Back to top |
|
|
|