Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts Skip file from delete pema_yozer All Other Mainframe Topics 3 Tue Aug 29, 2017 3:19 pm
No new posts Delete IMS remote transaction when MO... Sam Singh IMS DB/DC 0 Wed Aug 23, 2017 9:12 pm
No new posts Select numeric portion from CHAR data... balaji81_k DB2 6 Sat Aug 19, 2017 1:51 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us