Just do a left join with the delete query.
DELETE feed_tag FROM feed_tag LEFT JOIN feed ON
feed_tag.feed_id=feed.id WHERE feed.id IS NULL
That should do it. You can change "DELETE feed_tag" to "SELECT" and
test it first.
--
Brent Baisley
On Aug 13, 2008, at 4:51 PM, Daevid Vincent wrote:
> 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).
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
