Help with query to remove all records where foreign key doesn"t have corresponding records
Help with query to remove all records where foreign key doesn"t have corresponding records
am 13.08.2008 22:51:00 von Daevid Vincent
I want to remove all records from 'feed_tag' where the feed_id foreign =
key
doesn't have any corresponding records in feed.=20
For instance I may have a record in feed_tag that is like (23, 10, 4543,
'... (some date)').=20
Then lets say there is no record in feed that has a primary id key of =
10.=20
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` (
=A0 `id` int(11) NOT NULL auto_increment,
=A0 `title` varchar(100) default NULL,
=A0 `url` varchar(255) default NULL,
=A0 `host` varchar(100) default NULL,
=A0 `type` varchar(100) default NULL,
=A0 `status` char(1) default NULL,
=A0 `total_stories` int(11) default '0',
=A0 `created_at` datetime default NULL,
=A0 `updated_at` datetime default NULL,
=A0 PRIMARY KEY=A0 (`id`)
) ENGINE=3DInnoDB DEFAULT CHARSET=3Dutf8 ;
CREATE TABLE IF NOT EXISTS `feed_tag` (
=A0 `id` int(11) NOT NULL auto_increment,
=A0 `feed_id` int(11) default NULL,
=A0 `tag_id` int(11) default NULL,
=A0 `created_at` datetime default NULL,
=A0 PRIMARY KEY=A0 (`id`),
=A0 KEY `feed_tag_FI_1` (`feed_id`),
=A0 KEY `feed_tag_FI_2` (`tag_id`)
) ENGINE=3DInnoDB DEFAULT CHARSET=3Dutf8 ;
As you can see the foreign key 'feed_id' is the issue here (ignore the
tag_id key).=20
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
Re: Help with query to remove all records where foreign key doesn"t have corresponding records
am 13.08.2008 23:22:22 von Isart Montane
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-co nstraints.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 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=isart.montane@gmail.com
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
Re: Help with query to remove all records where foreign key doesn"t have corresponding records
am 14.08.2008 02:32:18 von Brent Baisley
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=brenttech@gmail.com
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org