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

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, and if 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)

CodePudding user response：

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));