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 |
|
Arulmanivannan
Starting Member
4 Posts |
 Posted - 2008-09-18 : 13:46:54
|
| Hi ! Help required !! What i need is a single query to make sure that it will perform a single insert and a hierarchical updates as a bottom to top manner !!!Explanation is For a table name Commission i need to insert the every data of the freshly joined member's name along with their respected referrer's name,Commission Amount and so on..Everytime i insert a new member i need to update his referrer's commission amount and referrer's referrer Commission amount and so on ..But i did this through a simple insert query and sequence of many update queries .... Is there any alternative way ??ThanX !!! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-18 : 13:48:53
|
| Can you illustrate the scenario with some sample data? Also are you using sql 2005? |
 |
|
|
Arulmanivannan
Starting Member
4 Posts |
Posted - 2008-09-18 : 14:05:13
|
| Sure this may help u !!!Initially Commission Amount is inserted as Zero "0" Membername Referername CommissionAmountROW 1 Aaron null 0after entering the second data thru insert query....CommissionAmount of my first data to be updated as 50So Membername Referername CommissionAmountROW 1 Aaron null 50Row 2 Babu Aaron 0after entering the third data thru insert query....CommissionAmount of my second data to be updated as 50 + 0(initial value) and my first data to be updated as 25 + 50(initial value) SO Membername Referername CommissionAmountROW 1 Aaron null 50 + 25Row 2 Babu Aaron 0 + 50Row 3 Cathy Babu 0and so on......Yes iam using SQL 2005 now !!!! Thanx for your reply...... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-18 : 14:07:14
|
| And whats the rule for determing what commision to be added ? |
|
|
|
Arulmanivannan
Starting Member
4 Posts |
Posted - 2008-09-18 : 14:13:35
|
| The rule for commisson to be added governed by Commission scale of 50, 25, 12.5, 10, 7.5, 5, 4, 3, 2, 150 for immediate reference25 for referrers reference12.5 for referrers referrers reference and so on ..... iam eager to reply ur questions ThanX |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-18 : 14:21:57
|
| I think you can use a recursive cte for this purpose. serach in this forums for recursive cte. you can get lots of examples. |
|
|
|
Arulmanivannan
Starting Member
4 Posts |
Posted - 2008-09-18 : 14:41:34
|
| ThanX dude !! I will search n if possible i can fw 2 u>> |
|
|
|
|
|
|
Avoid lumpsome queries