Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jvjean24601
Starting Member
2 Posts |
 Posted - 2005-11-04 : 17:50:15
|
| I'm having trouble trying to code a SQL update statement that interestingly enough follows the scenario very similar to the 'Six Degrees of Separation' premise.Consider that we have a table with the following data. We want to update the ACQUINTANCE column starting with KevinBacon. The only way I've been able to do this is by using a loop and going up the acquintance level one at a time. Is it at all possible to do this in one elegant and efficient SQL statement? PERSON A PERSON B ACQUINTANCE?John Jane ?Josh June ?Mark June ? Mark Gail ?Rick Gail ?Rick Kyra ?KevinBacon Kyra ?The target result set is:PERSON A PERSON B ACQUINTANCE?John Jane NoJosh June YesMark June Yes Mark Gail YesRick Gail YesRick Kyra YesKevinBacon Kyra Yes |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-05 : 00:07:04
|
| What is the logic you used to update values No or Yes on the ACQUINTANCE column?MadhivananFailing to plan is Planning to fail |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-11-05 : 08:04:26
|
| Looks similar to a hierarchy structure. Do the order of the rows matter? In other words if your (1st) row "John, Jane, ?" was "Kyra, Jane, ?" would Acquintance be Yes or No?Also, what's acquintance? Do you mean acquaintance?Be One with the OptimizerTG |
|
|
|
jvjean24601
Starting Member
2 Posts |
Posted - 2005-11-07 : 18:19:32
|
| Yes the order of the rows matter because Kyra does not know Jane. And Yes I meant 'acquaintance'. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-11-08 : 00:33:18
|
| If the degrees of separation (N) is a fixed number of levels and is not too deep, then a single statement with N self-joins might be best. If N is large or unknown, then you will want to loop through the recordset N times loading data into a temporary table, so you can weed out duplicates and keep the working dataset from exploding. |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2005-11-08 : 08:10:00
|
| In SQL Server 2005 you can use a recursive CTE to do transitive closure. e.g.http://www.windowsitpro.com/Article/ArticleID/46117/46117.htmlHmm, maybe not such a good example, since they've changed it subscriber only. Anyway, you can still download the zip file with the code in it. |
|
|
|
|
|
|
Six Degrees of Separation