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