Re: Analytics/update question

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 27 Sep 2005 11:54:37 -0700
Message-ID: <1127847277.405704.270340@g49g2000cwa.googlegroups.com>

Or, using the lag analytic function:

scott_at_ORA92> SELECT * FROM your_table
  2 /

        ID VALUE DATE_COL
---------- ---------- ---------

         1    -999.99 26-sep-05
         1       16.7 23-sep-05
         1        8.9 22-sep-05
         2       12.7 23-sep-05
         2       12.8 20-sep-05
         3    -999.99 19-sep-05
         3         44 15-sep-05
         3         76 14-sep-05

8 rows selected.

scott_at_ORA92> UPDATE your_table o
  2 SET o.value =

  3  	    (SELECT i.lag_val
  4  	     FROM   (SELECT id, value, date_col,
  5  			    LAG (value) OVER
  6  			      (ORDER BY date_col, id DESC) AS lag_val
  7  		     FROM   your_table) i
  8  	     WHERE  i.id = o.id
  9  	     AND    i.value = o.value
 10  	     AND    i.date_col = o.date_col)

 11 WHERE o.value = -999.99
 12 /

2 rows updated.

scott_at_ORA92> SELECT * FROM your_table
  2 /

        ID VALUE DATE_COL
---------- ---------- ---------

         1       16.7 26-sep-05
         1       16.7 23-sep-05
         1        8.9 22-sep-05
         2       12.7 23-sep-05
         2       12.8 20-sep-05
         3         44 19-sep-05
         3         44 15-sep-05
         3         76 14-sep-05

8 rows selected. Received on Tue Sep 27 2005 - 13:54:37 CDT