List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:August 13 2008 8:51pm
Subject:Help with query to remove all records where foreign key doesn't have corresponding records
View as plain text  
I want to remove all records from 'feed_tag' where the feed_id foreign key
doesn't have any corresponding records in feed. 

For instance I may have a record in feed_tag that is like (23, 10, 4543,
'... (some date)'). 

Then lets say there is no record in feed that has a primary id key of 10. 

I want that record (or usually records because of the 1 feed to many
feed_tag relationship) to be removed.

CREATE TABLE IF NOT EXISTS `feed` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(100) default NULL,
  `url` varchar(255) default NULL,
  `host` varchar(100) default NULL,
  `type` varchar(100) default NULL,
  `status` char(1) default NULL,
  `total_stories` int(11) default '0',
  `created_at` datetime default NULL,
  `updated_at` datetime default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS `feed_tag` (
  `id` int(11) NOT NULL auto_increment,
  `feed_id` int(11) default NULL,
  `tag_id` int(11) default NULL,
  `created_at` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `feed_tag_FI_1` (`feed_id`),
  KEY `feed_tag_FI_2` (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

As you can see the foreign key 'feed_id' is the issue here (ignore the
tag_id key). 

Thread
Help with query to remove all records where foreign key doesn't have corresponding recordsDaevid Vincent13 Aug
  • Re: Help with query to remove all records where foreign key doesn't have corresponding recordsIsart Montane13 Aug
  • Re: Help with query to remove all records where foreign key doesn't have corresponding recordsBrent Baisley14 Aug