Can I do a sort of DELETE with JOIN?

I have this kind of table in my MySql Database :

CREATE TABLE `forum_categories` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NOT NULL,
    `description` VARCHAR(255) NOT NULL,
    `date` DATETIME NOT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
ROW_FORMAT=DEFAULT

CREATE TABLE `forum_topics` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `category_id` INT(开发者_运维问答11) UNSIGNED NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `author` VARCHAR(255) NOT NULL,
    `date` DATETIME NOT NULL,
    `visits` INT(11) UNSIGNED NOT NULL DEFAULT '0',
    `sticky` TINYINT(11) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
ROW_FORMAT=DEFAULT

And I'd like, for example, to remove the category (from the table forum_categories) with id=4. But, when I do this, I'd like to remove all rows on the table forum_topics with category_id=4.

Is it possible to do a sort of DELETE+JOIN? Unfortunatly (as you can see) my host provider doesnt support InnoDB (what a shame..), so I can't use FOREIGN KEYS :(

SOLUTION

Solved with :

DELETE forum_categories.*, forum_topics.* , forum_visits.*, forum_messages.*
FROM forum_categories 
JOIN forum_topics ON forum_categories.id=forum_topics.category_id 
JOIN forum_visits ON forum_topics.id=forum_visits.topic
JOIN forum_messages ON forum_topics.id=forum_messages.topic_id
WHERE forum_categories.id=4

you can use the multi-table syntax also:

delete a.*, b.* from forum_categories a inner join forum_topics b on a.id = b.category_id where a.id = 4

Setup a TRIGGER to provide the "cascading" effect.

This MySQL cascading example should provide what you are looking for. It specifically calls out how to do it with MyISAM-based tables.

looks like you might be stuck with

DELETE FROM fourm_topics WHERE category_id = 4
DELETE FROM forum_categories WHERE id = 4

in the same call.

I addressed this question a while back

Mysql - delete multi table