Re: Update in a dynamic process

arnaud herve wrote:

> I've a table T like that
> X Y
> -------
> A NULL
> NULL C
> D E
> F NULL
>
> My Problem.
> I want to replace values equal to NULL by the value which follow them
> In my case, I'll get.
> A C
> D C
> D E
> F NULL
> I used a curosr but how to make a dynamic update?
>
> according to me,It would be like
> _____________________________________
> .........
> for i in 1..sql%rowcount-1 loop
> if X is null
> update T set X = select X from T
> where rownum = i+1;
> end loop;
> ..........
> _________________________________________
>
> it's a sample of code, I know a dirty code, but simply to show you, what I
> want to get.

In a practical sense what you are trying to do is impossible.

The reason is that Oracle tables are heap tables ... there is no organization of the data. So the row that follows during your first SQL query will not necessarily be the row that follows at any point in the future.

This could be caused by inserts, updates and deletes, it could be the result of using a different SQL statement. But in any case ... what you are trying to do is a testament to bad database design and you should come up with a design that numbers or timestamps your rows in such a manner that what you are trying to do can be done consistently ... or not at all.

Daniel Morgan Received on Mon Oct 14 2002 - 10:22:22 CDT