如何通过一个查询删除两个链接表(多对多关系)中的数据?

Question

有表films，actors和将它们绑定films_actors的表。

创建代码：

CREATE TABLE `actors` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`first_name` VARCHAR(50) NOT NULL,
`surname` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=9
;
CREATE TABLE `films` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255) NOT NULL,
`release` SMALLINT(4) NOT NULL,
`format` VARCHAR(10) NOT NULL,
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=6
;
CREATE TABLE `films_actors` (
`id_film` INT(11) NOT NULL,
`id_actor` INT(11) NOT NULL,
INDEX `FK_films_actors_actors` (`id_actor`),
INDEX `FK_films_actors_films` (`id_film`),
CONSTRAINT `FK_films_actors_actors` FOREIGN KEY (`id_actor`) REFERENCES `actors` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `FK_films_actors_films` FOREIGN KEY (`id_film`) REFERENCES `films` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

如果我从表中删除数据actors或films，那么数据将自动从films_actors表中删除，问题是一个查询是否可以一次从所有三个表中删除数据？

这是我尝试编写的查询，但没有成功。

delete
from films, actors
where actors.id in (
select films_actors.id_actor from films_actors 
where films_actors.id_film = 5
) and films.id = films_actors.id_film

Answer 1

您可以使用删除连接并使用连接中的子查询来管理 IN 子句

delete films.*, actors.*
from films
INNER JOIN (
select films_actors.id_actor, id_film
from films_actors 
where films_actors.id_film = 5
)  t  ON films.id = t.id_film
INNER JOIN  actors ON actors.id = t.id_actor 

Answer 2

你应该能够做到：

delete f, a, fa
from films f join
films_actors fa
on fa.id_film = f.id join
actors a
on fa.id_actor = a.id
where f.id = 5;

这似乎很危险，因为演员可能出现在其他电影中。 我假设你真的只想从f和fa中删除.