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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
