Re: tricky update query

This is better than my post... it is not dependent on rowids or the data being in any particular order.

• D.

On Fri, 16 Feb 2001 03:54:34 GMT, "The Ghost" <The_at_Ghost.com> wrote:

>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'))
>WHERE FLOW_TYPE='F'
>/
>
>"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 - 22:51:51 CST