Re: tricky update query

UPDATE FLOW A
SET FLOW_VAL=(SELECT FLOW_VAL FROM FLOW WHERE FLOWID=A.FLOWID

                    AND FLOW_TIME=(SELECT MAX(FLOW_TIME) FROM FLOW
                                    WHERE FLOWID=A.FLOWID
                                         AND FLOW_TIME < A.FLOW_TIME
                                   AND FLOW_TYPE='A'))

"ParK" <pxk40482_at_someplace.com> wrote in message news:Pine.OSF.4.21.0102152020280.27394-100000_at_Bayou.UH.EDU...
> 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 - 21:54:34 CST