DELETE FROM X WHERE value from X.column does not occur in Y.column

Question

Suppose a site categorizes popular stories according to the media format in which they are released. Its database consists of the following tables:

stories table

links table

media table

If a story is deleted from the database, its media associations should also be removed. If the only story associated with a certain type of media gets deleted, the information about that medium has now become useless and must also be deleted.

For example, if Spiderman (story_id 2) is deleted, its links to movie and comic media should also be removed. Because no other stories are released in the form of a comic, the information about comics as a medium must be removed, while any information on movies as a medium is still relevant for other stories and must remain untouched.

Abstractly, I thought of achieving this in the following way:

//delete the story itself:
DELETE FROM stories WHERE stories.story_id = 2

//delete its links to media:
DELETE FROM links WHERE links.story_id = 2

//delete any  media that might subsequently no longer have any links to them:
DELETE FROM media WHERE media.medium_id does not occur in links.medium_id

Or like this:

//delete the story itself:
DELETE FROM stories WHERE stories.story_id = 2

//fetch its associated media ids:
SELECT links.medium_id FROM links WHERE links.story_id = 2

//delete the story's links to any media:
DELETE FROM links WHERE links.story_id = 2

//consider deleting the types of media of which a story has just been deleted:
DELETE FROM media WHERE media.medium_id does not occur in links.medium_id and medium.medium_id = <previously fetched medium id(s)>

How would I actually write out these queries, or other queries, as part of, perhaps, a better approach?

score 3 · Answer 1 · edited May 23 '17 at 12:21

3

If your tables are set up with a FOREIGN KEY, then you might want to consider using ON DELETE CASCADE

Then when you delete from the story table, the items that are linked to that story will be deleted.

From MySQL Docs:

CASCADE: Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, do not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

SO user @Marc B wrote an excellent answer about setting this up:

MySQL foreign key constraints, cascade delete

Edit #1, your tables would be set up similar to this:

CREATE TABLE stories
    (`story_id` int not null, 
     `story_title` varchar(18), 
     `story_rating` varchar(9),
     primary key (story_id),
     key idx_story_id (story_id)
    ) ENGINE=InnoDB;

CREATE TABLE media
    (`medium_id` int not null, 
     `medium_name` varchar(9), 
     `medium_popularity` varchar(6),
     primary key (medium_id),
     key idx_medium_id (medium_id)
     ) ENGINE=InnoDB;


CREATE TABLE links
    (`link_id` int not null, 
     `story_id` int, 
     `medium_id` int,
     primary key (link_id),
     key ix_story_id (story_id), 
     key ix_medium_id (medium_id),
    FOREIGN KEY (story_id) REFERENCES stories(story_id)
       ON DELETE CASCADE,
    FOREIGN KEY (medium_id) REFERENCES media(medium_id)
       ON DELETE CASCADE
     ) ENGINE=InnoDB;

Then when you delete from the either the media or the stories table the corresponding records in the links table will be removed as well.

See SQL Fiddle with Demo

edited May 23 '17 at 12:21

Community

1
1

answered Jan 19 '13 at 13:05

Taryn

242,637
56
362
405

Thank you for your quick response! Cascade seems interesting, and I'm trying to set this up at the moment. In the links table, I had to index both the story_id and medium_id columns (or actually their equivalents in my database) before PHPMyAdmin would let me set any foreign keys. Now I've set the foreign key for medium_id successfully, but I am faced with an error for the story_id column: "Cannot add or update a child row: a foreign key constraint fails." i.imgur.com/AGp2F2F.png – Chris Jan 19 '13 at 14:01
Also, my plan is to set a foreign key in 'links'.'story_id' to 'stories'.'story_id', so the deletion of a story will trigger the deletion of its links. Next, I will set a foreign key in 'media'.'medium_id' to 'links'.'medium_id', so the deletion of a link will trigger the deletion of its medium, should it not be used elsewhere in the 'links' table. I guess that's what you meant? – Chris Jan 19 '13 at 14:30
1

@Chris see my edit, with some sample table scripts based on your sample data as well as a SQL Fiddle demo – Taryn Jan 19 '13 at 16:29
Thank you bluefeet, certainly a better way to go about deleting the links, but the fiddle doesn't deal with the cleanup part. That is to say, the comic medium is not deleted from 'media' when Spiderman - the only comic in the database - is deleted from 'stories'. I had hoped this: http://pastie.org/5732844#15-16,27-28,71 would work, but SQLFiddle doesn't take it. Any ideas? – Chris Jan 20 '13 at 10:59
@Chris Typically, when you delete a `story` you would not delete the `media` so it would be available for future stories, etc. If that is the process that you need then you will need to use a trigger to perform this. – Taryn Jan 20 '13 at 14:59
Understandable, but keeping those rows doesn't seem like a good idea in my application. I'll look into triggers. Do you see anything obviously wrong with using the cascade delete in the way set out in the SQL snippet I linked? – Chris Jan 20 '13 at 17:19

score 2 · Answer 2 · answered Jan 19 '13 at 13:11

2

Using InnoDB, bluefeet's method is the correct one. However, if it's just a one-off operation then the syntax is as follows...

DELETE x FROM table1 x LEFT JOIN table2 y ON y.id = x.id WHERE y.id IS NULL;

answered Jan 19 '13 at 13:11

Strawberry

33,750
13
40
57

For anyone interested, when applied to the example that would make for: DELETE `media` FROM `media` LEFT JOIN `links` ON `media`.`medium_id` = `links`.`medium_id` WHERE `links`.`medium_id` IS NULL – Chris Jan 20 '13 at 17:11
or DELETE x FROM media x LEFT JOIN links y ON y.medium_id = x.medium_id WHERE y.medium_id IS NULL; – Strawberry Jan 20 '13 at 17:16

score 1 · Answer 3 · answered Jan 19 '13 at 13:10

There's no reason to make the extra select query:

//delete the story itself:
DELETE FROM stories WHERE stories.story_id = 2

//delete its links to media:
DELETE FROM links WHERE links.story_id = 2

//delete any  media that might subsequently no longer have any links to them:
DELETE FROM media WHERE media.medium_id NOT IN((SELECT medium_id FROM link))

But, like @bluefeet said I would look into using ON DELETE CASCADE.

DELETE FROM X WHERE value from X.column does not occur in Y.column

3 Answers3