Wednesday, January 29, 2014

How to use the MERGE statement with a subquery

Here is an example on how to use the MERGE statement to update a table, using a self-referencing subquery. One of many ways to clean up unstructured data

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