I am trying to solve a connectivity problem. A simplification:
tblTemp
Section Connected_Section id
A B 1
B C 2
C D 3
D E 4
G H 5
H I 6
What I want to do is set the id so that it groups the above into
similarly labelled blocks, as below:
Section Connected_Section id
A B 1
B C 1
C D 1
D E 1
G H 5
H I 5
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.
similarly I have also tried
UPDATE tblTemp AS a, tblTemp AS b SET b.id=a.id WHERE
b.section=a.connected_section
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.
Please help
Si