Update a table with consecutive numbers

[mmolero]

I have a table such as

A	B	C	D
32	6100812	778899	123
32	6100812	778593	123
32	6100812	458962	123
33	8000812	885522	P111118
33	8000812	885537	P111118
32	6100915	990011	AA456
32	6100915	789684	AA456
32	6100915	485217	AA456

And I need to perform an update on C column using a consecutive number that i can create using DB2 function create Sequence. The problem is that i need to keep the number from incrementing while columns A and B doesn´t change their values.

So far, i have:

create sequence renumber
start with 1
increment by 1
no maxvalue
no cycle;

Update MYLIB.MYTABLE
Set
C = 'SP' || lpad((nextval for renumber),5,'0') ;

drop sequence renumber;

So, i get:

A	B	C	D
32	6100812	778899	SP00001
32	6100812	778593	SP00002
32	6100812	458962	SP00003
33	8000812	885522	SP00004
33	8000812	885537	SP00005
32	6100915	990011	SP00006
32	6100915	789684	SP00007
32	6100915	485217	SP00008

While what i really need is this:

	B	C	D
32	6100812	778899	SP00001
32	6100812	778593	SP00001
32	6100812	458962	SP00001
33	8000812	885522	SP00002
33	8000812	885537	SP00002
32	6100915	990011	SP00003
32	6100915	789684	SP00003
32	6100915	485217	SP00004

Can this be done in a single update? I am using this as embedded SQL on a SQLRPGLE program.

Thanks in advance for your help

[B.Hauser]

If you embedd it in SQL, why do you want to do it in an Single statement?
Even though I'm a big fan of reducing source code, for complex updates I prefer a cursor and multiple statements.
The following statement is a dynamic compound statement and can be executed from any SQL interface as it is.

Begin Atomic
  Create Sequence YourSchema.YourSequence AS SMALLINT;

  For V1 as CsrC01 Cursor                                                
      For Select Distinct D                    
             From YourTable
             Order By D                                              
          Do Update YourTable
                   set D   = 'SP' concat lpad((Nextval for YourSequence), 5, '0')
                   Where D = v1.D
                   With nc;
   End For;    

   Drop Sequence YourSchema.YourSequence;                                                      
End;

Why not embedding theses statements into either a stored procedure or an RPG procedure, so it can be called whenever necessary.

Birgitta