The NULLs in the second owner column are the indicators that there is no matching owner in the most recent three months.
Breaking down the query, the first part indicates what databases you want to delete records from:
DELETE FROM Events
Then you indicate which database you will be using to create the filter. In this case, Events and an alias of the Events table that I just called Owners. You are doing a left join so that you don't filter out any records from the first table, Events.
USING Events LEFT JOIN Events AS Owners
Since you are doing a join, you obviously need to specify a join condition. You are joining, based on OwnerID, the records from the Owners (Events) table that are younger than 3 months with ALL (because of the left join) records in the Events table.
ON Events.ownerID=Owners.ownerID AND Owners.eventDate>= 3 months ago
Anything that does not have a matching OwnerID from the Events/Owners join will have a NULL (or not match) "value" in the OwnerID column. Those are the ones you want tot delete, thus the last piece.
WHERE Owners.ownerID IS NULL
You should add a filter on Events for records older than 3 months. It will probably speed things up and will assure you don't delete newer records. So add this to the end, which I didn't have originally:
AND Events.EventDate< 3 months ago
Final delete statement: DELETE FROM Events USING Events LEFT JOIN Events AS Owners ON Events.ownerID=Owners.ownerID AND Owners.eventDate>= 3 months ago WHERE Owners.ownerID IS NULL AND Events.EventDate< 3 months ago
Original statement with typos and logic flaw:
DELETE
FROM Events
USING Events LEFT JOIN Events AS Owners
ON Events.ownerID=Owners.ownerID AND Events.eventData> 3 months ago <-- logic flaw, should be Owners.EventDate
WHERE Owners.ownerID IS NULL
Hope that helps.
On Nov 10, 2004, at 4:50 PM, Gerald Taylor wrote:
Thanks.... And I am liking that other answer although it has all nulls in the second owner column and I don't get how it works.
-- 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]
