List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:August 14 2008 12:32am
Subject:Re: Help with query to remove all records where foreign key doesn't have corresponding records
View as plain text  
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
>

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