[SOLVED] Need Help with DELETE query

[SAlexae]

Hi,

 

I am running the server logs of several websites through a MySQL database (version 4.1.22).  To keep the data clean and relevant, I'd like to delete obsolete records from the IPs table which has four fields:

 

member_id [smallint, unsigned, NOT NULL, default '0', INDEX]

site [char(6), default NULL]

ip_address [char(50), default NULL]

access_date [datetime, NOT NULL, default '1000-01-01 00:00:00']

 

What I would like to do is delete all records where 'access_date' is more than 3 months ago, but not the most recent entry for each member on each site should that entry have taken place more then 3 months ago.

 

I'm guessing that this can only be accomplished with nested queries and that I may also have to INDEX the 'site' field.  Any help would be much appreciated.

 

 

[wsantos]

You can try this.

 

DROP TEMPORARY TABLE IF EXISTS tmp_olderthanthreemonths;
CREATE TEMPORARY TABLE tmp_olderthanthreemonths SELECT DISTINCT member_id,site WHERE access_date < 'date';
DROP TEMPORARY TABLE IF EXISTS tmp_threemonths;
CREATE TEMPORARY TABLE tmp_threemonths SELECT DISTINCT member_id,site WHERE access_date > 'date';
DELETE FROM tmp_olderthanthreemonths USING tmp_threemonths WHERE tmp_threemonths.memberid = tmp_olderthanthreemonths.memberid AND tmp_threemonths.site = tmp_olderthanthreemonths.site;
DELETE FROM 'your table' USING tmp_olderthanthreemonths WHERE 'your table'.memberid = tmp_olderthanthreemonths.memberid;

 

Make sure you backup the data first.

 

And also use code tags.

[SAlexae]

Thanks!  I hadn't considered using temporary table before.  While your code wasn't *exactly* what I needed, it did put me on the right track and I was able to get the problem solved.  In case anyone's interested, here's how it worked out:

 

DROP TEMPORARY TABLE IF EXISTS tmp_lastaccess;
CREATE TEMPORARY TABLE tmp_lastaccess SELECT member_id, site, MAX( access_date ) AS last_access FROM ips GROUP BY member_id, site;

DROP TEMPORARY TABLE IF EXISTS tmp_obsoleteips;
CREATE TEMPORARY TABLE tmp_obsoleteips SELECT ips.member_id, ips.site, access_date, last_access FROM ips, tmp_lastaccess
WHERE ips.member_id = tmp_lastaccess.member_id
AND ( ips.site = tmp_lastaccess.site OR ( ips.site IS NULL AND tmp_lastaccess.site IS NULL ) )
AND access_date < DATE_SUB( NOW(), INTERVAL 3 MONTH )
AND access_date < last_access;

DELETE FROM ips1 USING ips AS ips1, tmp_obsoleteips
WHERE ips1.member_id = tmp_obsoleteips.member_id
AND ( ips1.site = tmp_obsoleteips.site OR ( ips1.site IS NULL AND tmp_obsoleteips.site IS NULL ) )
AND ips1.access_date = tmp_obsoleteips.access_date;

 

Thanks again.