List:General Discussion« Previous MessageNext Message »
From:Isart Montane Date:August 13 2008 9:22pm
Subject:Re: Help with query to remove all records where foreign key doesn't have corresponding records
View as plain text  
Hi Daevid

If you are using a foreign key you can set the reference as "cascade"
and when a row is deleted from feed it will be deleted from feed_tag.

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

If you don't like it you can delete it easy with a query like this

delete from feed_tag where id not in (select id from tag)


Best,
Isart



On Wed, Aug 13, 2008 at 5:51 PM, Daevid Vincent <daevid@stripped> 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