0

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!

  • It's not very clear what you are asking. Can you provide some sample (say 10) rows and what values you want? – ypercubeᵀᴹ Oct 28 '15 at 10:04
  • I did that with a sequence. Let's say max(SSC.NUMAR_CHEST) gives me 1221, lpad(max(SSC.NUMAR_CHEST),5,'0') gives me 01221 and I wanted to update my rows where NUMAR_CHEST with RO01221, RO01222, RO01223 but with that script it updated me only with RO01221 on all rows with null NUMAR_CHEST. I made a sequence and solved my problem, thanks! – WDrgn Oct 28 '15 at 14:21
1

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'));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.