title: SQL splitting a column and updating alternate table
body: <p>I'm working with a few different tables consisting of data from users rating movies. My movies table currently lists MovieId, MovieTitle, and Category. The data in each category column has repeating groups in each row that I want to eliminate by moving them over to MovieCategories which will consist of MovieId, CategoryId.</p>
  
  <p>My category column uses | as a delimiter, such as "Action | Thriller | Drama". I have a separate table CategoryDescription that assigns a CategoryId to each genre, so for example, I want my final MovieCategories table to look like this:</p>
  
  <pre><code> MovieId | CategoryId
  ------------------------
   1           1
   1           4
   1           7
  </code></pre>
  
  <p>and so on for each movie and their respective genres. Any help is greatly appreciated!</p>
  
  <p>Edit: Okay so  - desired result would be the table I have above, listing each movie and each individual genre. So for example, lets say in my movies table I have:</p>
  
  <pre><code>MovieId   |   MovieTitle    | Category
    1            Toy Story        Animated | Childrens
    2            Die Hard         Action | Thriller
  </code></pre>
  
  <p>I would want my MovieCategories table to have the MovieId and the corresponding CategoryId from my Categories table (which lists CategoryId 1-20 and each respective genre), so my result in my final table with the data above would be</p>
  
  <pre><code> MovieId     |   CategoryId
   1                  1
   1                  4
   2                  3
   2                  5
  </code></pre>
  
  <p>Hope that makes sense!</p>
  
  <p>One more edit:</p>
  
  <p>Thanks for the comments so far guys. I was able to get a query to produce almost the results I want, but how do I update my MovieCategories table now? Here's the query that worked: </p>
  
  <pre><code> select distinct
      DUMMYMOVIES.MovieId,
      trim(regexp_substr(DUMMYMOVIES.CatDescription, '[^|]+', 1, 
     levels.column_value)) as Category
    from 
      DUMMYMOVIES,
      table(cast(multiset(select level from dual connect by  level &lt;= 
     length (regexp_replace(DUMMYMOVIES.CatDescription, '[^|]+'))  + 1) as 
     sys.OdciNumberList)) levels
    order by MovieId;
  </code></pre>
  
  <p>This split the Category column correctly, but how do I get this over to my MovieCategories table? Can I use an UPDATE statement and have it run this query, checking the category name against my CategoryDescription table? </p>