Try something like this:
SELECT Events.ID, Events.ownerID, Owners.ownerID FROM Events LEFT JOIN Events AS Owners ON Events.ownerID=Owners.ownerID AND Events.eventData> 3 months ago WHERE Owners.ownerID IS NULL
I know you want to do a delete, but play with SELECT first to make sure it's doing what you want.
What the above query is doing is a self left join on owner IDs in the past three months. Thus, any owner with an event in the past three months will have a matching owner ID from the "Owners" table. You then create a filter for all events without a matching owner ID, value of NULL in the Owners.ownerID column.
So your result will look like this: Events.ID Events.ownerID Owners.ownerID ------------------------------------------------------------------------ 1 1 1 2 1 1 3 2 NULL 4 3 3 ....
So your DELETE statement would look something like this: DELETE FROM Events USING Events LEFT JOIN Events AS Owners ON Events.ownerID=Owners.ownerID AND Events.eventData> 3 months ago WHERE Owners.ownerID IS NULL
No guarantee that this is totally correct, but some quick testing seems to indicate it works.
On Nov 10, 2004, at 11:52 AM, Gerald Taylor wrote:
I have this table of events. Each event has an owner id and the time that it happened.
What I want to do is delete all events more than three months old but only if the owner does not own any newer events.
The coolest would just be a single DELETE query.
Can this be done? Mysql 4.0.18
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
