2

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 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:

A      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    SP00003

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

Me_
  • 61
  • 1
  • 6
  • Can you use a separate table that just has the distinct values of columns A and B? Put your sequence # in that table and join to it. – Andrew Feb 10 '17 at 15:47
  • Thanks, that might prove useful, but it would add running time to the program, since i would´ve to create the table for that on QTEMP library, with no data, then populate it with the distinct (A,B) values, update the sequence and finally update the original table via join. I´m trying to find more direct approach to the task at hand, considering that the program will be used often. – Me_ Feb 10 '17 at 19:09
  • 1
    The related table seems best. Now you'll have columns ( A, B ) = ( 32, 6100812 ) and the next is ( 33, 8000812 ). But some unknown future date, the table may have columns ( A, B ) = ( 32, 7777777 ) inserted. The sequential column D will then either be forever broken or every subsequent row will need to be somehow incremented per the count of inserted rows. Re-architecting the database to meet relational concepts could save much future trouble and help performance. – user2338816 Feb 11 '17 at 07:22

1 Answers1

2

merge into MYLIB.MYTABLE ot
using (SELECT A, B, 'SP'||LPAD((ROW_NUMBER() OVER ()),5,'0')
rownum
FROM MYLIB.MYTABLE group by a,b
order by a,b

) as nt(a,b,rownum)
on ot.a = nt.a and ot.b=nt.b
when matched then
update set d=nt.rownum
else ignore