How to insert multiple data with different parameters/conditions into multiple columns?

Question

Hello everyone!

I created a table with a four columns, these columns will hold different movie types.

What I am trying to do is insert a movie into a specific column based on the movie code.

In this example below, I am using the movie code to specify a which movies are inserted in the Action Movie column.

INSERT INTO MOVIE(
ACTION_MOVIE
)
SELECT MOVIE_NAME
FROM MOVIE_LIST
WHERE MOVIE_CODE = '10001';

So how do I insert multiple movies into multiple columns? What if I wanted to insert movies in an Action Movie and Horror movie using different movie code? Movie code 10001 is for actions movies and movie 10002 is for horror movies.

Thanks everyone!

Synth

Answer 1

INSERT INTO MOVIE(
ACTION_MOVIE, HORROR_MOVIE
)
SELECT CASE WHEN MOVIE_CODE IN ('10001','10003') THEN MOVIE_NAME
		END Action_Movie
	, CASE WHEN MOVIE_CODE IN ('10001','10002') THEN MOVIE_NAME
		END Horror_Movie
FROM MOVIE_LIST

You can use something like the above, but I'm curious as to why you are creating a table like this.  It might be better to create a Movie Table, a Category table, and a MovieCategory association table.

From there if you want to see the above table layout, you would create a query to display it that way.

---

Best Wishes, The Redman; If something helps, please help show it by voting, if it solves, bonus!

Answer 2

INSERT INTO MOVIE (ACTION_MOVIE)

SELECT MOVIE_NAME FROM MOVIE_LIST WHERE MOVIE_CODE = '10001'

GO

INSERT INTO MOVIE(HORROR_MOVIE)

SELECT MOVIE_NAME FROM MOVIE_LIST WHERE MOVIE_CODE = '10002'

OR..

INSERT INTO MOVIE(ACTION_MOVIE, HORROR_MOVIE)

SELECT CASE WHEN MOVIE_CODE = '10001' THEN MOVIE_NAME ELSE NULL END

,           CASE WHEN MOVIE_CODE = '10002' THEN MOVIE_NAME ELSE NULL END

FROM MOVIE_LIST

WHERE MOVIE_CODE IN ('10001','10002')

Just for curiosity:

Why are you creating a table to identify movies by type if you already have it under MOVIE_CODE ?

---

karepa

Answer 3

INSERT INTO MOVIE(
ACTION_MOVIE, HORROR_MOVIE
)
SELECT CASE WHEN MOVIE_CODE IN ('10001','10003') THEN MOVIE_NAME
		END Action_Movie
	, CASE WHEN MOVIE_CODE IN ('10001','10002') THEN MOVIE_NAME
		END Horror_Movie
FROM MOVIE_LIST

You can use something like the above, but I'm curious as to why you are creating a table like this.  It might be better to create a Movie Table, a Category table, and a MovieCategory association table.

From there if you want to see the above table layout, you would create a query to display it that way.

---

Best Wishes, The Redman; If something helps, please help show it by voting, if it solves, bonus!

I completely agree with you but the table has already been designed by someone else and this is why I am trying to break it down.

Answer 4

INSERT INTO MOVIE (ACTION_MOVIE)

SELECT MOVIE_NAME FROM MOVIE_LIST WHERE MOVIE_CODE = '10001'

GO

INSERT INTO MOVIE(HORROR_MOVIE)

SELECT MOVIE_NAME FROM MOVIE_LIST WHERE MOVIE_CODE = '10002'

OR..

INSERT INTO MOVIE(ACTION_MOVIE, HORROR_MOVIE)

SELECT CASE WHEN MOVIE_CODE = '10001' THEN MOVIE_NAME ELSE NULL END

,           CASE WHEN MOVIE_CODE = '10002' THEN MOVIE_NAME ELSE NULL END

FROM MOVIE_LIST

WHERE MOVIE_CODE IN ('10001','10002')

Just for curiosity:

Why are you creating a table to identify movies by type if you already have it under MOVIE_CODE ?

---

karepa

Because the movie code does not identify an actual category and can be open to interpretation.
Also what I posted is simply a brief example, the table is a many to many and so users have multiple ID's in the table

Thank you so much, I have learn something I never knew how to do.

Synth

Answer 5

As GGoldspnk said create movie table,movie category table.. separately.

---

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you