Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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...