Re: Update table.

On 18 Sep 2002 05:07:33 -0700, kenneth.osenbroch_at_canaldigital.com (Kenneth Osenbroch) wrote:

>Greetings.
>
>Hopefully, some of you can help me with the following scenario:
>
>Table A : Column A | Column B
> . | .
> Value 4 | Value 5
> Value 6 | Value 7
> Value 8 | Value 9
> . | .
>
>I need to uptdate Table A to become:
>
>Table A : Column A | Column B
> . | Value 4
> Value 4 | Value 6
> Value 6 | Value 8
> Value 8 | .
>
>Ergo, I need to take the value from the the first row and update the
>secound row - 1 with it. Any ideas on how to do this?
>
>Thanks,
>Kenneth

Hi Kenneth,

I assume that ColumnA is unique, and that "second row" and "first row" refer to ColumnA sorted ascending :

update TABLEA a set
ColumnB = (select ColumnB - 1 from TABLEA b

      where b.ColumnA = (select min(ColumnA) from TABLEA c where c.ColumnA > a.ColumnA))
where ColumnA != (select max(ColumnA) from TABLEA);

Note that the last column is not updated, because it has no successor...

• Kenneth Koenraadt