Tables look as follows:
MAJOR | COURSE | CLASS | CREDITS | ROOM_NO | TEACHER |
---|---|---|---|---|---|
Nuclear Science | Physics A | A. Brown, Room 208 | 8 | ||
Nuclear Science | Mathematics A | M. Stevens, Room 56 | 8 | ||
Nuclear Science | Advanced Algebra | K.Wise, Audit. 20 | 10 | ||
English Literature | Ex. Phil | A.Peterson, Room 208 | 8 | ||
English Literature | Early British Lit | A.Peterson, Room 208 | 8 | ||
English Literature | Greek Mythology | D.Simmons, Room 206 | 10 | ||
Nuclear Science | Physics A | Goodman, Room 208 | 8 |
To populate the ROOM_NO and TEACHER columns, I used the following query:
MERGE INTO COURSE_CATALOG A USING (SELECT CLASS FROM COURSE_CATALOG) B ON (A.ROWID = B.ROWID) WHEN MATCHED THEN UPDATE SET A.TEACHER = REGEXP_REPLACE(REGEXP_SUBSTR(CLASS, '[[:alpha:]]+.{1,}[^[:digit:]]',1,1,'i'), ', Room |, Audit.', ''), A.ROOM_NO = REGEXP_SUBSTR(B.CLASS,'[[:digit:]]+'), A.CLASS = NULL ; COMMIT;Result:
MAJOR | COURSE | CLASS | CREDITS | ROOM_NO | TEACHER |
---|---|---|---|---|---|
Nuclear Science | Physics A | 8 | 208 | A. Brown | |
Nuclear Science | Mathematics A | 8 | 56 | M. Stevens | |
Nuclear Science | Advanced Algebra | 10 | 20 | K.Wise | |
English Literature | Ex. Phil | 8 | 208 | A.Peterson | |
English Literature | Early British Lit | 8 | 208 | A.Peterson | |
English Literature | Greek Mythology | 10 | 206 | D.Simmons | |
Nuclear Science | Physics A | 8 | 208 | Goodman |
No comments:
Post a Comment