Update an Oracle DB with a max number for the null rows I have an Oracle db and I need to update some columns and one of them is an number that has to be some kind of an id - max number on that column. If I use something like this:

update ss_chest ssc
    set SSC.NUMAR_CHEST = 'RO' || (select lpad(max(SSC.NUMAR_CHEST),5,'0')
                                   from ss_chest ssc where SSC.NUMAR_CHEST is not null)
where SSC.NUMAR_CHEST is null;


And the result is always the same and it updates my table with the same max NUMAR_CHEST. How can I update it always with the max number? (I have to concatenate my number with RO in front and with leading 0 on 5 spaces)
Thanks!
Is this a one-time update, or are you planning on running that query after inserts, where the insert statement is not setting SSC.NUMAR_CHEST? If you want ascending numbers, it sounds like you need to use an Oracle sequence; your subquery (select lpad(...  will return the max value at the point in time of the start of your query.  

To use a sequence, create it once (using the name SEQ_CHEST in the examples below), then reference it in your update; every time you access SEQ_CHEST.nextval you will get a unique number:

create sequence SEQ_CHEST maxvalue 99999;  -- one time deal

update ss_chest ssc
   set SSC.NUMAR_CHEST = 'RO' || lpad(SEQ_CHEST.nextval,5,'0')
   where SSC.NUMAR_CHEST is null;


and your insert statements would be like

insert into ss_chest values('A', 'B', ... 'RO' || lpad(SEQ_CHEST.nextval,5,'0'));