Re: tricky update query

You did say the data was ordered:

update flowthings A set flowval = (
 select flowval from flowthings B
 where rowid = (select max(rowid) from flowthings C  where C.flowtime < A.flowtime and C.flowtype = 'A'  ))
 where A.flowtype = 'F'

SQL> select * From flowthings;

   FLOWID FLOWTIME FLOWVAL F
--------- -------------- --------- -

        1 02/10/01 08:00        15 A
        1 02/10/01 09:00        15 F
        1 02/10/01 10:00        17 A
        1 02/10/01 11:00        17 F
        1 02/10/01 12:00        17 F

On Thu, 15 Feb 2001 20:53:43 -0600, ParK <pxk40482_at_someplace.com> wrote:

>Everyone,
>
>Help needed on building a query with the following condition:
>
>certain rows, which satisfy a condition in a where clause, needs to have
>their attribute values updated by those present in the previous row.
>
>Consider, for example, a table with 4 columns
>flowid flowTime flowVal flowtype
>------------------------------------------------------
>001 02/10/01 8:00am 15 A
>001 02/10/01 9:00am 12 F
>001 02/10/01 10:00am 17 A
>001 02/10/01 11:00am 23 F
>001 02/10/01 12:00pm 10 F
>.....
>.....
>
>Here flowId and FlowTime is the key.
> We have an entry in the table for every hour, for all flowId's.
>So the problem is I need to UPDATE the flowValue, whose FlowType is
>"F" with the FlowVal of the row immediately above this row, whose flow
>type is not "F"
>
>For example, the FlowVal of row# 2 should be "15", because the flowType of
>this row is "F" and the row immediately above it has flow type not equal
>to "F"
>
>Similarly rows 4 & 5, should have a flow value of "17"
>
>How can this be done in a single query? The values are already sorted in
>the table and the entry is for every hour on the hour.
>
>Any help is appreciated. Please post the reply to newsgroup only.
>
>Thanks in advance.
>
>
>
>
Received on Thu Feb 15 2001 - 22:36:46 CST