CODEWITHSUNDEEP
mysqlsqlmany-to-manysql-delete
Gildars
Gildars

Reputation: 144

How to delete data in two linked tables (many-to-many relationship) with one query?

There are tables films,actors and the table binding them films_actors.

CREATE code:

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
;

If I delete data from the tables actors orfilms, then the data will automatically be deleted from the films_actors table, the question is whether one query can delete data from all three tables at once?

Here is the query that I tried to write, but without success.

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

Upvotes: 1

Views: 50

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133380

You could use an delete join and use a subquery in join for manage the IN clause 

    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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270513

You should be able to do:

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;

This seems dangerous, because actors could be in other films. I assume you only really want to delete from f and fa.

Upvotes: 1

Related Questions