Re: Another SQL headbanger

["Brian Johnson" <brian.johnson.mn@xxxxxxxxx>]

update thetable
set Date = :newdate,
line = 1 + ( select coalesce( max( line ) , 0 )
    from thetable
    where EmpID = :empid and Date = :newdate )
where empid = :empid
    and date = :olddate
    and line = :line


On 9/6/07, Pete Helgren <Pete@xxxxxxxxxx> wrote:
> I have another issue where I am updating a file using SQL and it could
> potentially create duplicates.  Rather than omit the duplicates, I want
> to modify the values so the duplicates won't occur.  There is a line
> number field in the table that I can increment by 1 if I find a record
> that is a duplicate and that will eliminate a duplicate value.
>
> For example the data might be
>
> EmpID      Date       Line#
> 446          071507    1
> 446          071507    2
> 446          072507    1
>
> So if I want to change the date from 072507 to 071507 I also need to
> change the line to 3.
>
> Any ideas ?
>
> Pete Helgren
>
> --
> This is the RPG programming on the AS400 / 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.