如何在通用查询的帮助下进行数据的更新/迁移

Question

我正在努力改进sql，但我的一项工作有一些问题。所以我想到了下面这种情况。我有三张桌子：

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 |
+------------+-----------+

但过了一段时间，我有要求将课程划分为更具体的名称。

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

所以在COURSE表中，它看起来像

+----+--------------------+
| 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   |
+----+--------------------+

当我陷入困境时，要知道我是站在正确的位置上的。因为我也需要更新STUDENT_COURSE。很明显，我可以用硬编码的值来做这件事，但在表中我有数千个条目的情况下，这将花费很多时间。所以我在考虑更通用的方法。Student包含一个存储LEVEL的列，它和课程名称的后缀相对应。所以我想用它。

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 ) 

知道我该怎么做这种迁移吗(我想称之为迁移太多了(

Answer 1

我认为表之间存在父子关系，因此首先需要备份课程表和student_curse表。

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

然后您可以分别截断student_scourse和课程表。

truncate table student_course;
truncate table course;

使用交叉产品创建每一个可能的课程。

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

最后，你可以更新你的学生课程表。

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