Re: lag and update
Date: Thu, 17 Jul 2008 04:30:09 -0700 (PDT)
On 17 Jul., 12:51, Serge Rielau <srie..._at_ca.ibm.com> wrote:
> steph wrote:
> > On 16 Jul., 22:28, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> >> 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
> >> )
> > Thanks a lot, this works perfect :-)
> Are you sure? You have no WHERE clause for the UPDATE.
> So you will update every row in the table.
> If your production table grows much bigger than what you test this may
> come back to haunt you.
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab
Well, I have a where clause for the update - for simplicity I omitted
it in my little example.
If you refer to the inexistent where clause for the inner select containing the lag() - might be a performance issue running this for every row I update. I'll have a look at the execution plan later.
stephan Received on Thu Jul 17 2008 - 06:30:09 CDT