CREATE table W_S_SCORE
(
F_CODE  Varchar2(3),
E_SCORES varchar2(2)
S_SCORE varchar2(2)
);
 
INSERT INTO W_S_SCOREvalues ('12A', '08','01');
INSERT INTO W_S_SCOREvalues ('12A', '09','04');
INSERT INTO W_S_SCOREvalues ('12A', '10','05');
INSERT INTO W_S_SCOREvalues ('12A', '07','05');
INSERT INTO W_S_SCOREvalues ('12C', '08','01');


Table 2 :
 
CREATE TABLE A_STUD
(
l_code varchar2(6),
f_code varchar2(3),
E_SCORE VARCHAR2(2),
S_SCORES VARCHAR2(2)
);
 
INSERT INTO A_STUD (l_code ,f_code ,E_SCORE) VALUES ('2028','12A','08');
INSERT INTO A_STUD (l_code ,f_code ,E_SCORE) VALUES ('2029','12C','19');
INSERT INTO A_STUD (l_code ,f_code ,E_SCORE) VALUES ('2030','12C','20');
INSERT INTO A_STUD (l_code ,f_code ,E_SCORE) VALUES ('2031','12C','10');
INSERT INTO A_STUD (l_code ,f_code ,E_SCORE) VALUES ('2032','12A','08');
INSERT INTO A_STUD (l_code ,f_code ,E_SCORE) VALUES ('2037','12C','07');
INSERT INTO A_STUD (l_code ,f_code ,E_SCORE) VALUES ('2038','12C','10');


TABLE HAS DATA AS FOLLOWS:

l_code f_code       E_sCORES    S_SCORE
---------------------------------------------------------
2028    12A            08

2029    12C            19

---
---
---

nOW REQUIRED O/P SHOULD BE , S_SCORE COLUMN SHOULD UPDATE BASED ON E_SCORES DATA

l_code  f_code       E_sCORES    S_SCORE
---------------------------------------------------------
2028    SCOTT          08            01

2029     GARMIN       19              13

{CODE}

If, In A_STUD table  S_SCORE is missing then it should retrieve S_SCORE value automatically from W_S_SCORE table based on the E_SCORE in A_STUD table.

l_code  f_code       E_sCORES    S_SCORE
---------------------------------------------------------
2028    SCOTT          08            01

2029     GARMIN       19              13

Table1 :  A_STUD		

L_CODE	F_CODE	E_SCORE	W_S_SCORE
935	10B	8	
1033	10B	8	
1029	10B	8	
904	10B	9	
932	10B	9	
1040	10B	8	
966	10B	9	
715	10B	10	
1053	10B	22	
938	10B	31	

F_CODE	E_SCORE	S_SCORE 
12A	8	1
12A	9	5
12A	10	6
12A	11	7
12A	12	8
12A	13	10
12A	14	10
12A	15	11
12A	16	11
12A	17	12
12A	18	13
12A	19	14
12A	20	14
12A	21	15
12A	30	23
12C	9	4
12C	10	5
12C	11	6
12C	12	7
12C	13	9
12C	14	9
12C	15	10
12C	16	10
12C	17	11

A_STUD

L_CODE	F_CODE	E_SCORE	W_S_SCORE
935	10B	8	 1
1033	10B	8	 1
1029	10B	8	 1
904	10B	9        4	
932	10B	9	 4
1040	10B	8	 1
966	10B	9	 4
715	10B	10	 5
1053	10B	22	 15
938	10B	31       23

l_code  f_code       E_sCORES    S_SCORE
---------------------------------------------------------
2028      12A          08            01

2030      10B          09            04
2032      10B          10            05
2034      10C          8             01
2036      10C          22            15

MERGE INTO  E_stud   dst
USING (
          SELECT distinct a.f_code
          ,       e.s_score
          FROM    E_stud   a
          JOIN    E_SCORES  e  ON  e.e_score  = a.e_score
      )              src
ON    (dst.f_code  = src.f_code)
WHEN MATCHED THEN UPDATE
SET   dst.w_s_score  = src.s_score
;