Re: Deleting with SQL

On 20-Apr-2015 06:38 -0500, John R. Smith, Jr. 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.

So apparently there is no primary key. Is the column really only a DATE, or is that a TIMESTAMP? Little matter really, because if there is no unique identifier, then the means to determine the row to delete must rely on the RRN :-( Just that there could be for example, 100 entries with the day of the cutoff and perhaps just 10 since, so which 20 of those older entries that remain and which 70 of those newer entries that are purged would be unpredictable.

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?

The following is untested for syntax and thus clearly also untested for function, but I expect should be functional for the scenario described:

create table logwrp
( who_ran_pgm for wrp char(10)
, pgm_run_dat for prd date
)
; -- given table expressed with DDL to give meaning to example
create view logwrp_rn as
( select
rrn(L) as reln
, row_number() over (order by prd desc) as ordn
from logwrp as L
/* if truly a hardened rule then FETCH FIRST 30 ROWS ONLY */
)
; -- separate OLAP as view for direct query + later reference
set :purge_prior = '2015-02-15'
; -- expressed as HV to allow adjustment
set :retain_days = 30
; -- expressed as HV to allow adjustment
delete
from logwrp as D
where pgm_run_dat < :purge_prior /* older than given date */
and rrn(D) = ( select reln
from logwrp_rn
where ordn > :retain_days
) /* or older than last n */
; -- locate RRN by the date-ordered numbered rows