CODEWITHSUNDEEP
sqldb2ibm-midrangerpgle
Me_
Me_

Reputation: 61

DB2 update with Sequence Number but only increasing when condition happens

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

Upvotes: 2

Views: 2223

Answers (1)

user7392562
user7392562

Reputation:

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

Upvotes: 2

Related Questions