> I am trying to solve a connectivity problem. A simplification:
Glad to see this, I've been fighting a similar problem.
[color=blue]
> I have used the following
>
> UPDATE (tblTemp AS a INNER JOIN tblTemp AS b ON
> b.section=a.connected_section) SET b.id=a.id
>
> This works recursively upto a point and then fails to change some
> records. Leaving the only option of rerunning the above query over and
> over.[/color]
Which is what I ended up doing. You can examine the RecordsAffected
property of the querydef (if you put it in a query) or the database (if
you .Execute it) to see if you still have work left to do.
[color=blue]
> Is there anyway that I can make this follow through the whole table.
> Some groups have about 120 connections so I don't want to have to loop
> the query 120 times. Also the records are not in any order and sorting
> will not put them into a correct order.[/color]
You don't want to... There is another approach. Use an intermediate
table Progress(section, id)
For every record
is Section in Progress? ("left side")
yes-> change tblTemp.id into Progress.id
is there more than one matching record, with different ids, in
Progress? ("merger")
yes-> update tblTemp changing highest id into lowest
change Progress changing highest id into lowest
no -> is Connected_Section in Progress? ("right side")
yes-> change tblTemp.id into Progress.id
do same merger check as above
no -> add Section and Connected_Section to Progress
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea
