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
 

 

Help required in DB2 Delete Query

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

New User


Joined: 05 Jun 2006
Posts: 19

PostPosted: Thu Sep 24, 2009 11:29 am    Post subject: Help required in DB2 Delete Query
Reply with quote

I have a requirement where I have to select maximum effective date <= current _date for a given group and then delete the rows for the group with effective date < maximum effective date obtained in the previous query.

As of now, I am able to achieve this task by using the below 2 queries.

Query 1:

SELECT DISTINCT A,
B,
C,
D,
E,
F,
G,
MAX(DATE_EFF)
FROM Table1
WHERE DATE_EFF <= CURRENT_DATE
GROUP BY A,B,C,D,E,F,G

Query2:

DELETE FROM Table1
WHERE A = &A
AND B = &B
AND C = &C
AND D = &D
AND E = &E
AND F = &F
AND G = &G
AND DATE_EFF < &MAX(DATE_EFF) /* This is obtained from the previous query */

Please let us know how they can be merged into a single query.
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Sep 24, 2009 1:04 pm    Post subject:
Reply with quote

Code:
delete from table1 O
where date_eff < (select max(I.date_eff) from table1 I
      where I.A = O.A
        and I.B = O.B
        and I.C=O.C
         ...
        and I.G = O.G)


I know : it looks scary deleting with just the date in the where clause.
but it should work. Always test it yourself.


as an extra : no need to write distinct when using group by. (I hate queries with distinct in them)
Back to top
View user's profile Send private message
dubasir

New User


Joined: 05 Jun 2006
Posts: 19

PostPosted: Thu Sep 24, 2009 5:25 pm    Post subject: Thanks for your reply
Reply with quote

Thanks for your quick help. It eased our work.

Thanks again.
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts JCL to delete component in a package sundaram.naveen Compuware & Other Tools 14 Tue Nov 29, 2016 6:21 pm


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