Re: SQL error on duplicates

Have you tried the WHENEVER logic?
http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/rzajp/rzajpmstssile.htm#HDRSSILE

Or,
UPDATE XAMCNTP SET MCDTUP = MCDTUP + 1000000 
WHERE MCDTUP < 600000 and (mcdtup + 1000000) not in (select mcdtup + 
1000000 from xamcntp) 

Rob Berendt
-- 
"They that can give up essential liberty to obtain a little temporary 
safety deserve neither liberty nor safety." 
Benjamin Franklin 




Dan Rasch <drasch@xxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
04/24/2003 08:57 AM
Please respond to Midrange Systems Technical Discussion
 
        To:     Midrange Systems Technical Discussion 
<midrange-l@xxxxxxxxxxxx>
        cc: 
        Fax to: 
        Subject:        SQL error on duplicates



We have SQL in an RPG program that attempts to add a century code in
front of a date.  The date is part of the key.  But when the century 
code is added, on some records, it creates a duplicate key.  Most 
of the dates in the file are ok, but some have already had the century
code added, without deleting the original record. 

We get a duplicate key on this statement:

UPDATE XAMCNTP SET MCDTUP = MCDTUP + 1000000 
WHERE MCDTUP < 600000 

This causes an CPF5009 error, and MONMSG does not ignore it.

Is there a way to handle dup keys with SQL?

TIA,

Dan Rasch - because if the human species concentrated on the really 
important things in life, there would be a shortage of fishing poles!
IBM Certified twice....... but still a couple PTFs away from Nirvana.

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo.cgi/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.