Re: Updating a column based on a 'Windows Function' formula

RJ schrieb:
> Hi All,
>
> I want to make the following update:
>
> update TIME_TABLE a
> set a.end_date_time = lead(a.start_date_time) over (order by
> a.start_date_time)
>
> what this does is it takes the next records start time and uses it as
> an end time.
> If i use this in a select statement the results are perfect, but I cant
> update records using this function.
>
> How can I update the end_date_time column?
>
> Thanks in advance :)
>

If your start_date_time is unique, you can do something like this:

UPDATE Time_Table a
SET a.End_Date_Time = (SELECT End_Date_Time

                           FROM   (SELECT Start_Date_Time,
                                          Lead(Start_Date_Time) 
Over(ORDER BY Start_Date_Time) End_Date_Time
                                   FROM   Time_Table) t
                           WHERE  t.Start_Date_Time = a.Start_Date_Time);

Best regards

Maxim Received on Wed Nov 15 2006 - 03:37:23 CST