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