Is there MySQL syntax for joining more than three tables when inserting?-CodePudding

Here is my query:

INSERT INTO film_new
(SELECT title, rental_rate,length,film_id,length,release_year FROM film)
JOIN film_actor
    ON film.film_id = film_actor.film_id
JOIN actor
    ON film_actor.actor_id = actor.actor_id

and I get the error

JOIN is not expected

CodePudding user response：

The problem is that your parenthesis syntax here is wrong, instead of

INSERT INTO film_new 
(SELECT title, rental_rate,length,film_id,length,release_year FROM film) 
JOIN film_actor ON film.film_id = film_actor.film_id 
JOIN actor ON film_actor.actor_id = actor.actor_id;

you should enclose the inner query completely, like

INSERT INTO film_new 
(SELECT (title, rental_rate,length,film_id,length,release_year) FROM film
JOIN film_actor ON film.film_id = film_actor.film_id 
JOIN actor ON film_actor.actor_id = actor.actor_id)

The reason SQL is telling you that JOIN is not expected is because it is not part of the inner query (due to the way you put parentheses) but on the outer one, which in turn does not have a FROM clause, and therefore does not accept any join.

CodePudding user response：

Remove all brackets!

INSERT INTO film_new
SELECT title, rental_rate, length, film_id, length, release_year
FROM film
JOIN film_actor ON film.film_id = film_actor.film_id
JOIN actor ON film_actor.actor_id = actor.actor_id

I have just reformatted your query to make it readable, and removed brackets to make it syntactically correct.

However, your select columns look incorrect, especially that you don't been to be selecting anything from the joined tables.