Re: lag and update

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Thu, 17 Jul 2008 06:51:41 -0400
Message-ID: <6e8mi1F5quf2U1@mid.individual.net>

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.

Cheers
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Received on Thu Jul 17 2008 - 05:51:41 CDT