INSERTING RECORDS USING CTE

Aamir Mohiuddin 0
Viewed 36

I am trying to insert the records in a table using subquery but I have to repeat that subquery again and again. Now I tried the same using CTE but I am missing something (it is giving me some syntax error).

The query I wrote is:

WITH 
mySubQuery AS(
SELECT film_id FROM film WHERE film.title="WEST LION"
),
INS AS (
INSERT INTO film_actor(actor_id, film_id) 
VALUES ((SELECT actor_id FROM actor WHERE actor.first_name = "DAN" AND actor.last_name="TORN"), mySubQuery),
    ((SELECT actor_id FROM actor WHERE actor.first_name = "MAE" AND actor.last_name="HOFFMAN"), mySubQuery),
    ((SELECT actor_id FROM actor WHERE actor.first_name = "SCARLETT" AND actor.last_name="DAMON"), mySubQuery)
)
  SELECT * FROM mySubQuery,INS;

Error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO film_actor(actor_id, film_id) 
VALUES((SELECT actor_id FROM acto' at line 6

but when I insert the data as:

INSERT INTO film_actor(actor_id, film_id) VALUES 
    ((SELECT actor_id FROM actor WHERE actor.first_name = "DAN" AND actor.last_name="TORN"), (SELECT film_id FROM film WHERE film.title="WEST LION")),
    ((SELECT actor_id FROM actor WHERE actor.first_name = "MAE" AND actor.last_name="HOFFMAN"), (SELECT film_id FROM film WHERE film.title="WEST LION")),
    ((SELECT actor_id FROM actor WHERE actor.first_name = "SCARLETT" AND actor.last_name="DAMON"), (SELECT film_id FROM film WHERE film.title="WEST LION"));

It works fine.

common-table-expression mysql sql
1 Answers
INSERT INTO film_actor(actor_id, film_id) 
SELECT actor.actor_id, film.film_id
FROM actor
CROSS JOIN film
WHERE (actor.first_name, actor.last_name) IN ( ('DAN',      'TORN'),
                                               ('MAE',      'HOFFMAN'),
                                               ('SCARLETT', 'DAMON') )
  AND film.title = 'WEST LION' 
;
Akina0
Related