Re: lag and update

steph schrieb:
> hello group,
>
> 10g:
>
> i've got this table "z" with following attributes:
>
> id
> dat ... date value, not necessarily consecutive
> k ... some numeric value
> k_vt
>
> the first 3 columns are filled, k_vt shall be filled with value of k
> of the previous day.
>
> I can't do it like this because of ora-30483:
>
> update t
> set k_vt=lag(k,1,null) over (partition by id order by id,dat)
>
> This also does not work, as the subselect understandably always
> returns NULL
>
> update z z1
> set k_vt=
> (
> select lag(k,1,null) over (partition by id order by id,dat)
> from z z2
> where z1.id=z2.id
> and z1.dat=z2.dat
> )
>
> I'm thinking about using some sort of staging table to solve this - or
> how could i do this within one update statement?
>
> thanks,
> stephan

Just nest your subquery one level more:

update z z1
set k_vt=(select k_vt

           from (select
                 id,
                 dat,
                 k,
                 lag(k,1,null) over(partition by id order by dat) k_vt
                 from z)
           where z1.id=id and z1.dat=dat and z1.k=k
           )

Btw, you don't need to order by id if you partition by id. If the combination of id,dat and k is not unique, then you may get more than one row in your subquery, it can be workarounded, if you update based on rowid.

Best regards

Maxim Received on Wed Jul 16 2008 - 15:28:47 CDT