CODEWITHSUNDEEP
mysqlsql
Michael Geiser
Michael Geiser

Reputation: 365

SQL to delete all rows with timestamp older than (now - x days) except keep the most recent n records

If I have the table below, I need to delete all records older than (now - x days) but that are not in the most recent n records.

Concrete example: users cannot reuse password for 90 days and may not reuse the last 10 passwords. If I change my password every 90 days, I still cannot reuse a password for 10 changes. 

CREATE TABLE PASSWORD_HISTORY (
            ID BIGINT NOT NULL AUTO_INCREMENT,
            USER_NAME VARCHAR(255) NOT NULL,
            PASSWORD VARCHAR(255) NOT NULL,
            SALT VARCHAR(255),
            CREATED_TIMESTAMP BIGINT NOT NULL,
            UPDATED_TIMESTAMP BIGINT NOT NULL,
            TENANT_ID INTEGER DEFAULT -1234,
            PRIMARY KEY (ID)
)ENGINE INNODB;

Upvotes: 2

Views: 529

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

If I understand correctly, you want to delete records who most recent records are older than 90 days and whose passwords are not in the most recent 90 days. I'm not sure why you actually need to delete these. You can impose those rules when you query the data, rather than in the data itself. Then you can easily change the rules to 120 days, if you like.

In any case, this would be much simpler if the passwords were just enumerated for each user. But they are not, and MySQL does not support row_number(). That suggests using variables to enumerate the results. Because of the rules of MySQL, this requires a subquery and a join in the delete. The rest is just basic conditional logic:

delete ph
    from password_history ph join
         (select ph2.*,
                 (@rn := if(@u = user_name, @rn + 1,
                            if(@u := user_name, 1, 1)
                           )
                 ) as seqnum
          from password_history ph2 cross join
               (select @rn := 0, @u := '') params
          order by user_name, created_timestamp desc
         ) ph2
         on ph2.id = ph.id
    where ph2.seqnum > 10 and
          ph.created_timestamp <= date_sub(curdate(), interval 90 day)

Upvotes: 1

DiscipleMichael
DiscipleMichael

Reputation: 510

DELETE 
FROM PASSWORD_HISTORY
Where PASSWORD_HISTORY.ID Not IN

(
    SELECT ID FROM(SELECT pass1.[USER_NAME], Count(*) num, pass1.ID
    FROM Password_History pass1 JOIN Password_History pass2
        ON pass1.[USER_NAME] = pass2.[USER_NAME] AND pass1.ID <= pass2.ID
    Group BY pass1.[USER_NAME], pass1.ID
    Having Count(*) <=  10

) a 

)
AND CREATED_TIMESTAMP <= Convert(int, DATEADD(day, -90, GetDate()))

This will only delete the rows with the lowest ID's if those rows belong to a username with more than 10 records AND those rows have a Created_TimeStamp of more than 90 days ago. Of course,you should run this with a SELECT * in place of the DELETE to make sure it's giving you what you want. Additionally, I would run with a Begin Transaction; and be prepared to Rollback Transaction; if it goes sour on the delete.

Upvotes: 1

Drew
Drew

Reputation: 24960

-- drop table password_history;
create table password_history
(
id bigint null auto_increment primary key,
user_name varchar(255) not null,
created_timestamp bigint not null
);

-- delete from password_history where id>0; -- safe mode sometimes barfs
insert password_history (user_name,created_timestamp) values ('fred',100);
insert password_history (user_name,created_timestamp) values ('fred',200);
insert password_history (user_name,created_timestamp) values ('fred',300);
insert password_history (user_name,created_timestamp) values ('fred',400);
insert password_history (user_name,created_timestamp) values ('fred',401);
insert password_history (user_name,created_timestamp) values ('fred',402);
insert password_history (user_name,created_timestamp) values ('fred',403);
insert password_history (user_name,created_timestamp) values ('fred',404);
insert password_history (user_name,created_timestamp) values ('fred',405);
insert password_history (user_name,created_timestamp) values ('fred',406);
insert password_history (user_name,created_timestamp) values ('fred',407);
insert password_history (user_name,created_timestamp) values ('fred',500);
insert password_history (user_name,created_timestamp) values ('fred',555);

insert password_history (user_name,created_timestamp) values ('fred',unix_timestamp(now()) );
insert password_history (user_name,created_timestamp) values ('stan',unix_timestamp(now()) );

alter table password_history add deleteMe int;

select * from password_history;

-- variables n and d
-- n=10, users last 10 records
-- d=90, last 90 days
-- rows where password created more than 90 days ago (replace 90 below as desired)
select * from password_history 
where unix_timestamp(now()) - created_timestamp>(60*60*24*90)
order by id desc

-- update password_history set deleteMe=1 where id>0;  -- safe mode sometimes barfs

-- update password_history set deleteMe=null where id>0; -- safe mode sometimes barfs

update password_history
join 
(
select ph.id,ph.user_name,ph.created_timestamp
from password_history ph
join
(
select ph.id,ph.user_name,ph.created_timestamp 
from password_history ph
join
(
select user_name,max(id),max(created_timestamp),count(*) as theCount
from password_history xx
group by user_name
having theCount>10
) inR2
on ph.user_name=inR2.user_name
order by ph.user_name,ph.created_timestamp desc
limit 10
) inR1
on ph.id=inR1.id
) bigThing
on password_history.id=bigThing.id
set deleteMe='1'
where password_history.id>0 -- this gets rid of the safe mode barfing

update password_history
join 
(
select user_name from password_history p2 where p2.deleteMe=1
) phMany
on password_history.user_name=phMany.user_name
set deleteMe=2
where password_history.deleteMe is null

-- select * from password_history order by user_name,created_timestamp desc;

-- look at the ones with deleteMe=2

Upvotes: 1

Related Questions