Re: Tricky self join query help?
I noticed a few mistakes in my query, which may be causing some confusion and would probably cause it not to work. But I'll break everything down. 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]
Re: Tricky self join query help?
If you post the table structure (SHOW CREATE TABLE tablename\G) we could help you write this statement. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Gerald Taylor [EMAIL PROTECTED] wrote on 11/10/2004 11:52:35 AM: 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]
Re: Tricky self join query help?
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]
Re: Tricky self join query help?
The parts I am interested in: (I won't bore you with the fields not relevant to this problem ) CREATE TABLE events ( e_id int(15) NOT NULL auto_increment, e_owner int(15) NOT NULL default '0', e_time int(15) NOT NULL default '0', other junk omitted PRIMARY KEY (e_id) ) TYPE=MyISAM; 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. When I write applications that delete I always back up the table and use a copy or a small sample on a play database. [EMAIL PROTECTED] wrote: If you post the table structure (SHOW CREATE TABLE tablename\G) we could help you write this statement. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Gerald Taylor [EMAIL PROTECTED] wrote on 11/10/2004 11:52:35 AM: 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
