IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Help required in DB2 Delete Query


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts DELETE SPUFI DB2 1
No new posts DSNTIAUL driven delete IBM Tools 0
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts How to delete a user's alias from the... JCL & VSAM 11
Search our Forums:

Back to Top