SQL & PL/SQL

I am trying to come up with the query which updates the column in one table with the value from other table after it finds a correct match.

So, below are the create table and sample data.

create table code1 
(
    codeid number,
    codedesc varchar2(60)
);

Insert into code1 values ( 1,'R1 CONTRACTS');

Insert into code1 values ( 2,'R2 CONTRACTS');

Insert into code1 values ( 3,'R3 CONTRACTS');

Insert into code1 values ( 4,'R5 CONTRACTS');

Insert into code1 values ( 5,'R9 CONTRACTS');

Insert into code1 values ( 6,'R10 CONTRACTS');

create table table1 
(   
    tablekey number,
    prefix  varchar2(25),
    codedesc    varchar2(60)
);

Insert into table1(tablekey,prefix) values (1,'1001PAC');

Insert into table1(tablekey,prefix) values (2,'1001MXT');

Insert into table1(tablekey,prefix) values (3,'1002PAE');

Insert into table1(tablekey,prefix) values (4,'1003PCS');

Insert into table1(tablekey,prefix) values (5,'1004BDX');

Insert into table1(tablekey,prefix) values (6,'1005PAC');

Insert into table1(tablekey,prefix) values (7,'1006PAC');

Insert into table1(tablekey,prefix) values (8,'1007LDR');

Insert into table1(tablekey,prefix) values (9,'1009LCR');

Insert into table1(tablekey,prefix) values (10,'1010LBR');

Insert into table1(tablekey,prefix) values (11,'ABCDEF');

I am trying to write a query which would update the value of column - codedesc (currently Null) of table table1 after it matches a string of column - codedesc of table code1.

The logic to match the string is, - Take - 2nd position value of code table- codedesc column and get 2 character. So when the string is - R1 CONTRACTS', the string will be 1. ( Select substr('R1 CONTRACTS',2,2) from dual ). - Output will be 1.

Now,
look into the table1 for 3rd position of prefix that matches the string returned from above query. So if the prefix is '1001PAC', it should look for 2 digit value starting from 3rd position. So, in this case it will be 01. Numerically 01 and 1 are equal, so match is found for that row in table table1, so we will need to update the value of column codedesc with 'R1 Contracts'.

    tablekey,    prefix            codedesc    
---------- ------------------------------------------------------------

      1               1001PAC     R1 CONTRACTS    -- Needs to be update with this value. 
      2               1001MXT     R1 CONTRACTS
      3               1002PAE      R2 CONTRACTS
...
      11             ABCDEF                                --Null ( No Need to update when no match found).

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Appreciate your help.