Re: Deleting with SQL

It appears that at least with 7.1, you can delete from a view that uses
ROW_NUMBER()...

create view myview
as ( select
row_number() OVER (partition by pgmname order by entrydate) as
rownum
, a.*
from mytable A
);

delete from myview
where rownumber > 30 and entrydate <= '2015-02-15';

Too bad DB2 for i doesn't appear to support deleting from a CTE...though
LUW apparently does. As does SQL Server,,,


Charles

On Mon, Apr 20, 2015 at 9:37 AM, John R. Smith, Jr. <
smith5646midrange@xxxxxxxxx> wrote:

Although that second line sounds wrong, I think that is a correct
interpretation of the end results.

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Charles Wilt
Sent: Monday, April 20, 2015 8:47 AM
To: Midrange Systems Technical Discussion
Subject: Re: Deleting with SQL

So for every program, you'll end up with...
either
- >= 30 entries, starting at 2/15/2015
- <= 30 entries, going back as far as needed? (assuming it's possible that
you don't have 30 entries currently logged)



Charles

On Mon, Apr 20, 2015 at 7:38 AM, John R. Smith, Jr. <
smith5646midrange@xxxxxxxxx> wrote:

I have a file that is a log of who ran programs on what date that I
need to purge but the purge has dual criteria. I need to purge based
on keeping x number of entries and entries before x date. For
example, I need to keep all entries after 02/15/2015 AND also the last
30 entries. So, if an entry falls inside of either of these criteria,
I keep it. The user is immaterial to the result set.



I'm not sure I have explained that very well but in the event that I
have, can anyone tell me how to do it through SQL?



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.