Posted: Wed Sep 23, 2009 4:21 am Post subject: Help regarding delete query
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.
Please help how to get the required output using a single delete statement.
Code:
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
Delete the records for a given A,B,C,D,E,F,G and DATE_EFF < MAX(DATE_EFF) obtained in the previous query
Code:
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)
The OP used this solution (provided on another board):
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)
If that is the implementation that the OP actually used, then it ( obviously) didn't satisfy his originally posted requirements, which stated that the max(date_eff) used as a predicate for the delete must be <= current_date. _________________ A computer once beat me at chess, but it was no match for me at kick boxing.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
