Single-row subquery returns more than one row for update query

I am bit stuck with this not getting how do I write the code.

I have table called STUDENT with this data in it:

STUDID | NAME | SURNAME | STUDENT_FILE       | CLASS 
------- ------ --------- -------------------- -------
154    | Vil  | Docs    | \\demo\\F\\A.csv   | A  
132    | Nil  | Fics    | \\do\\F\\X.csv     | A
123    | Sag  | Ter     | \\de\\F\\Y.csv     | A
178    | Mia  | Jac     | \\mo\\F\\Z.csv     | A
167    | Dic  | Pol     | D.csv              | A
134    | Mic  | Kil     | (null)             | A

My expected result is in this below format where I need to remove all path name and keep only file name in column STUDENT_FILE

STUDID | NAME | SURNAME | STUDENT_FILE       | CLASS 
------- ------ --------- -------------------- -------
154    | Vil  | Docs    | A.csv              | A  
132    | Nil  | Fics    | X.csv              | A
123    | Sag  | Ter     | Y.csv              | A
178    | Mia  | Jac     | Z.csv              | A
167    | Dic  | Pol     | D.csv              | A
134    | Mic  | Kil     | (null)             | A

My code:

DECLARE 
   CNT NUMBER 
BEGIN
     SELECT COUNT(STUD_ID) INTO CNT FROM STUDENT;
     
     FOR i...CNT LOOP
     UPDATE STUDENT SET STUDENT_FILE=(SELECT SUBSTR(STUDENT_FILE,(INSTR(STUDENT_FILE,'\',-1,1) 1),LENGTH(STUDENT_FILE)) AS FL FROM STUDENT) where CLASS='A' and ROWNUM :=i;    
     END LOOP;
END;
/

I'm getting an error

Single-row subquery returns more than one row

Any solution is much appreciated or how can it be created as a generic stored procedure?

CodePudding user response：

You don't need PL/SQL, only an ordinary UPDATE:

UPDATE student
   SET student_file =
          SUBSTR (student_file,
                  (  INSTR (student_file,
                            '\',
                            -1,
                            1)
                     1),
                  LENGTH (student_file))
 WHERE class = 'A'

By the way, rownum = i is just a wrong way to access any row, it is never true (unless i = 1).

Also, a simpler option is to use

UPDATE student
   SET student_file =
          SUBSTR (student_file, INSTR (student_file, '\', -1)   1)
 WHERE class = 'A';