Re: SQL Update duplicate records

["Rettig, Roger" <roger.rettig@xxxxxxxx>]

Have you tried putting a distinct in your sub-select?

UPDATE MYFILE B SET B.DATE = '2013-07-18'
 WHERE
B.PALLET IN ( SELECT  *DISTINCT *B.PALLET
 From MYFILE B, MYFILE C
    Where B.PALLET = C.PALLET and RRN(B) > RRN(C)
    AND B.DATE = '2013-07-19'
    AND C.DATE = '2013-07-19')


On Mon, Jul 22, 2013 at 9:31 AM, Milyard, William A. <
William_Milyard@xxxxxxxxxxxx> wrote:

> I know I've seen this in the archives but I can't find the solution.  I
> need to update one field where the entire row is a duplicate. For example:
>
> Pallet Date
> 915     2013-07-17
> 915     2013-07-19  Need to change to 2013-07-18
> 915     2013-07-19
> 918     2013-07-19
> 920     2013-07-19  Need to change to 2013-07-18
> 920     2013-07-19
>
>
> UPDATE MYFILE B SET B.DATE = '2013-07-18'
>  WHERE
> B.PALLET IN ( SELECT B.PALLET
>  From MYFILE B, MYFILE C
>     Where B.PALLET = C.PALLET and RRN(B) > RRN(C)
>     AND B.DATE = '2013-07-19'
>     AND C.DATE = '2013-07-19')
>
>
> I've tried several variations of this statement but it always updates too
> many records. I know there other ways to fix this problem but I have 9
> files with hundreds of records so SQL seems to be the best solution.
> Any help is appreciated.
>
> Thanks, Bill
>
>
>
>
>
>
> ________________________________
> Notice from Bob Evans Farms: This e-mail message, including any
> attachments, may contain confidential information that is intended only for
> the person or entity to which it is addressed. Any unauthorized review,
> use, disclosure or distribution is strictly prohibited. If you are not the
> intended recipient, please contact the sender by reply e-mail and destroy
> all copies of the original message and any attachments.
> --
> This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
> mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.