Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to perform delete for a selected value in column

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
mf_arun

New User


Joined: 16 Jun 2006
Posts: 1

PostPosted: Fri Jun 16, 2006 2:15 pm    Post subject: How to perform delete for a selected value in column
Reply with quote

I need to perform delete for a selected value in column. From the result table, again I need to have deletion only for certain no. of rows. I tried the following query:
----------------------------------------------------------------------------------
DELETE FROM (SELECT col FROM Table1
WHERE col = 'value'
ORDER BY col
FETCH FIRST 3 ROWS ONLY)
----------------------------------------------------------------------------------
in QMF

I 'm getting following error message while trying to execute the above mentioned query.

----------------------------------------------------------------------------------
QUERY MESSAGES:
SQL error at or before ( (line 3, position 13).
----------------------------------------------------------------------------------

Can u please help me on the same. Or can u suggest any other way of doing the same.
Back to top
View user's profile Send private message

prasadvrk

Active User


Joined: 31 May 2006
Posts: 200
Location: Netherlands

PostPosted: Fri Jun 16, 2006 5:26 pm    Post subject: Re: Urgent : DB2 Delete Query
Reply with quote

mf_arun wrote:
I need to perform delete for a selected value in column. From the result table, again I need to have deletion only for certain no. of rows. I tried the following query:
----------------------------------------------------------------------------------
DELETE FROM (SELECT col FROM Table1
WHERE col = 'value'
ORDER BY col
FETCH FIRST 3 ROWS ONLY)
----------------------------------------------------------------------------------
in QMF

I 'm getting following error message while trying to execute the above mentioned query.

----------------------------------------------------------------------------------
QUERY MESSAGES:
SQL error at or before ( (line 3, position 13).
----------------------------------------------------------------------------------

Can u please help me on the same. Or can u suggest any other way of doing the same.



I think you should change your query to

DELETE FROM Table1 A WHERE PRIMARYCOL IN
(
SELECT PRIMARYCOL FROM Table1 B
WHERE 3=
(
SELECT COUNT(*) FROM Table1 B
WHERE B.PRIMARYCOL > A.PRIMARYCOL
)
) ;

You may have to tweak this query a bit to get your desired result.
Please let me know if this query meets your req or not
Back to top
View user's profile Send private message
prasadvrk

Active User


Joined: 31 May 2006
Posts: 200
Location: Netherlands

PostPosted: Fri Jun 16, 2006 5:32 pm    Post subject:
Reply with quote

May be you need to change your query as

DELETE FROM Table1 A WHERE PRIMARYCOL IN
(
SELECT PRIMARYCOL FROM Table1 B
WHERE 3=
(
SELECT COUNT(*) FROM Table1 B
WHERE B.COL ='value'
)
) ;

Let me know if this helps
Back to top
View user's profile Send private message
prasadvrk

Active User


Joined: 31 May 2006
Posts: 200
Location: Netherlands

PostPosted: Fri Jun 16, 2006 5:44 pm    Post subject:
Reply with quote

I think you need both so your query should look like
DELETE FROM Table1 A WHERE PRIMARYCOL IN
(
SELECT PRIMARYCOL FROM Table1 B
WHERE 3 >
(
SELECT COUNT(*) FROM Table1 B
WHERE B.COL ='value' AND
B.PRIMARYCOL > A.PRIMARYCOL
)
) ;
Back to top
View user's profile Send private message
prasadvrk

Active User


Joined: 31 May 2006
Posts: 200
Location: Netherlands

PostPosted: Fri Jun 16, 2006 6:24 pm    Post subject:
Reply with quote

Some more improvement to your query , please try this


DELETE FROM Table1 A WHERE PRIMARYCOL IN
(
SELECT PRIMARYCOL FROM Table1 B
WHERE 3 =
(
SELECT COUNT(*) FROM Table1 B
B.PRIMARYCOL > A.PRIMARYCOL
)
AND WHERE B.COL ='value'

) ;
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. DB2 query Using Recursion, Converting... smilewithashu2 DB2 3 Tue Jan 03, 2017 12:50 pm
No new posts JCL to delete component in a package sundaram.naveen Compuware & Other Tools 14 Tue Nov 29, 2016 6:21 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 6 Sun Nov 06, 2016 8:11 pm
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm
This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us