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: 1281
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 Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm


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