Update query

[Wes1961]

I have a data like

F1 F2 F3
A B
B C
B D
B E .5
AA BB
BB CC
CC DD
CC EE
CC FF .62

I need to update the value of F3 based on a value that is alrady in F3. In the sample above I would like all records, A-B, B-C, B-D, be set to the value in the record B-E.

CAn someone help me with this?

 

[PHV]

You may try this:
UPDATE yourTable
SET F3 = (SELECT F3 FROM yourTable WHERE F1='B' AND F2='C')
WHERE F1 & '-' & F2 In ('A-B','B-C','B-D')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Wes1961]

PH,

Thanks for your quick response. I am sorry that I was unclear on what I wanted.

After the update I would expect the table to look like:

F1 F2 F3
A B .5
B C .5
B D .5
B E .5
AA BB .62
BB CC .62
CC DD .62
CC EE .62
CC FF .62

I don't know how many levels that the value F3 can be down, however, I want to update all records that have any association with the record that contains the value.

The rocord B - E contains the vlaue that I want to use. So then I want to update any record in F1 that coantins B. Then I want to use the contents of F1 (B) in F2 to determine if any more records need updating. So, I would then want to update record A (because of the A-B-E link) with the value of the E i.e ".5"

Thanks,
Wes

My mind works like lightning. One brilliant flash and it's gone.