How to do a string operation on all data of a column in an Oracle database table?

Question

I have a table like give bellow (table name IDN_CONSUMERS),

| USER_ID | USERNAME  |
| 001A    | ABC/Anne  |
| 034K    | ABC/John  |
| DF23    | ABC/Peter |

I need to remove the ABC/ part of the all username and put the modified value back to the table, so the final result should be like,

| USER_ID | USERNAME |
| 001A    | Anne     |
| 034K    | John     |
| DF23    | Peter    |

I tried the following script, but it's not working for a reason I don't understand. Can anyone please help me with this ?

DECLARE 
NEWUSERNAME VARCHAR2(512);

BEGIN

FOR ID IN (SELECT USER_ID FROM IDN_CONSUMERS) LOOP

SELECT SUBSTR((SELECT USERNAME FROM IDN_CONSUMERS WHERE USER_ID='||ID||'), 4) INTO NEWUSERNAME FROM DUAL;

UPDATE IDN_CONSUMERS SET USERNAME='||NEWUSERNAME||' WHERE USER_ID='||ID||';

END LOOP;

END;

Answer 1

There's no need to write a loop or to store the new user name value separately.

UPDATE IDN_CONSUMERS SET USERNAME = SUBSTR(USERNAME, 4);