Update SQL with changing keyfield value

Update a keyfield in a file that contains multiple keyfields.

Have you ever come across this problem? You want to update a keyfield in a file that contains multiple keyfields.

It is quiet easy until the SQL-statement won't result in a duplicate key error. Here's the statement to bypass:

 Update Filename f1
set Keyvalue2 = Keyvalue2 + 1
where 0 = 
  (Select count(*)
     from filename f2
    where f2.Keyvalue1 = f1.Keyvalue1
      and f2.Keyvalue2 = f1.Keyvalue1+1)

==================================
MORE INFORMATION ON THIS TOPIC
==================================

The Best Web Links: tips, tutorials and more.

Ask your programming questions--or help out your peers by answering them--in our live discussion forums.

Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.


This was first published in July 2002

Dig deeper on iSeries CL programming

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchEnterpriseLinux

SearchDataCenter

Close