How to do update/migration of data, with help of generic query

I am in middle of trying to get better with sql, but I have some issue/question with one of my exercies. So I thought about this kind of following situation. I have three tables:

STUDENT

+----+-------+----------+
| ID | NAME  |  LEVEL   |
+----+-------+----------+
|  1 | Tom   | beginner |
|  2 | Peter | expert   |
|  3 | Kate  | beginner |
|  4 | John  | beginner |
+----+-------+----------+

COURSE

+----+-----------+
| ID |   NAME    |
+----+-----------+
|  1 | Math      |
|  2 | English   |
|  3 | French    |
|  4 | Chemistry |
+----+-----------+

STUDENT_COURSE

+------------+-----------+
| STUDENT_ID | COURSE_ID |
+------------+-----------+
|          1 |         1 |
|          1 |         2 |
|          3 |         1 |
|          4 |         3 |
+------------+-----------+

But after some time I have requirments to divide courses into more specific names.

Math      -> Math_Beginner, Math_Expert
English   -> English_Beginner, English_Expert
French    -> French_Beginner, French_Expert
Chemistry -> Chemistry_Beginner, Chemistry_Expert

So in COURSE table whould looks like that

+----+--------------------+
| ID |        NAME        |
+----+--------------------+
|  1 | Math_Beginner      |
|  2 | English_Beginner   |
|  3 | French_Beginner    |
|  4 | Chemistry_Beginner |
|  5 | Math_Expert        |
|  6 | English_Expert     |
|  7 | French_Expert      |
|  8 | Chemistry_Expert   |
+----+--------------------+

And know I am in point when I am stuck. Because I need to update STUDENT_COURSE too. Obviously I can do it with hardcoded values, but in case of table where I have thousends of entries it would take a lot of time. So I was thinking about more generic approach. Student does containt column where LEVEL is stored, and it is corresponding with suffix of course name. So I would like to use it.

 UPDATE student_course
SET    course_id = ( here i would need TO have CURRENT value OF course.NAME
AND    student.level,
ANDIF it was math
AND    level IS beginner i would put math_beginner ) 

Any idea how could I do this kind of migration(I suppose calling it migration is too much)

Answer

I presume there are parent – child relations between tables, so first you need to backup your course and student_course tables.

create table student_course_bck select * from student_course;
create table course_bck as select * from course

Then you can truncate student_course and course tables respectively.

truncate table student_course;
truncate table course;

With cross product create every single possible courses.

insert into course select rownum, de  from (select distinct(c.namex||'_'||initcap(s.levelx)) de from student s cross join course_bck c order by 1);

And finally you can update your student_course table.

insert into student_course (select * from (with levelx_tab as(select s.idx studentid, s.namex studentname, s.levelx studentlevel, c.idx courseid, c.namex coursename, c.namex||'_'||initcap(s.levelx) newcoursename from student s join student_course_bck sc
on s.idx = sc.STUDENT_ID
join course_bck c
on c.idx = sc.COURSE_ID) select t.studentid, cs.idx from levelx_tab t join course cs
on t.newcoursename = cs.namex));