Should I use cursors for this update??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Should I use cursors for this update???

Author

Topic

sahu74
Posting Yak Master

100 Posts

Posted - 2004-09-17 : 09:53:41

[code]
I have 4 tables with the following structure:

Tbl1( Tbl1_ID, DeptID, StartYear, EndYear)

Tbl2( Tbl2_ID, Tbl1_ID, StartYear, EndYear)

Tbl3( Tbl3_ID, Tbl2_ID, StartYear, EndYear)

Tbl3_Term (Tbl3_ID, TermID, Value)

[/code]

I am adding a new column to Tbl1, Tbl2 and Tbl3. The modified structure is:

Tbl1( Tbl1_ID, DeptID, StartYear, EndYear, OrderNo) **Tbl1_ID is the primary key; DeptId is the foreign key

Tbl2( Tbl2_ID, Tbl1_ID, StartYear, EndYear, OrderNo) **Tbl2_ID is the primary key; Tbl1_ID is the foreign key

Tbl3( Tbl3_ID, Tbl2_ID, StartYear, EndYear, OrderNo) **Tbl3_ID is the primary key; Tbl2_ID is the foreign key

Tbl3_Term (Tbl3_ID, TermID, Value) **Tbl1_ID and TermID together are the primary key; Tbl3_ID is the foreign key

I would like to group everything first based on the termID. Then I want to update the new column in Tbl1, Tbl2 and Tbl3 based on (group by) the foreign key in each table (i.e., DeptID for Tbl1, Tbl1_ID for Tbl2 and Tbl2_ID for Tbl3).

For all the records associated with DeptID 'x' in Tbl1, I want the OrderNo to be updated as 1, 2, 3, 4,...For the next

DeptID (say 'z') in Tbl1 the OrderNo will start with 1, 2, 3 ... again.

For Tbl2, the OrderNo for all the records associated with Tbl1_ID should be 1, 2, 3, ....and the same way goes on for each Tbl1_ID in Tbl2.

I donot need to update the OrderNo in Tbl1, Tbl2 or Tbl3 if the EndYear value corresponding to that record is < Current year (2004)

Here is some sample data.
************************
[code]

Tbl1

Tbl1_ID DeptID StartYear EndYear OrderNo
1 z 2001 2005
2 x 2002 2005
3 x 2002 2005
4 z 2002 2005
5 z 2002 2005
6 x 2002 2005

--------------------------------------------------------
Tbl2

Tbl2_ID Tbl1_ID StartYear EndYear OrderNo
1 1 2001 2005
2 1 2001 2005
3 2 2002 2005
4 3 2002 2005
5 4 2002 2005
6 4 2002 2005
7 3 2002 2003

--------------------------------------------------------

Tbl3

Tbl3_ID Tbl2_ID StartYear EndYear OrderNo
1 1 2002 2005
2 3 2002 2005
3 3 2002 2005
4 2 2002 2005
5 4 2002 2005
6 3 2002 2003
7 4 2002 2005
8 4 2002 2005
9 6 2002 2005
10 5 2002 2005
11 6 2002 2002
12 6 2002 2005

------------------------------------------------------

Tbl3_Term

Tbl3_ID TermID Value
------- ------ -----
1 1 12
1 2 14
2 1 10
2 3 17
3 2 22

******************************************************
Tables after update
******************************************************
Tbl1

Tbl1_ID DeptID StartYear EndYear OrderNo
1 z 2001 2005 1
2 x 2002 2005 1
3 x 2002 2005 2
4 x 2002 2005 3
5 z 2002 2005 2
6 z 2002 2005 3

------------------------------------------------------------------------
Tbl2

Tbl2_ID Tbl1_ID StartYear EndYear OrderNo
1 1 2001 2005 1
2 1 2001 2005 2
3 2 2002 2005 1
4 3 2002 2005 1
5 4 2002 2005 1
6 4 2002 2005 2
7 3 2002 2003 -

------------------------------------------------------------------------

Tbl3

Tbl3_ID Tbl2_ID StartYear EndYear OrderNo
1 1 2002 2005 1
2 3 2002 2005 1
3 3 2002 2005 2
4 2 2002 2005 1
5 4 2002 2005 1
6 3 2002 2003 -
7 4 2002 2005 2
8 4 2002 2005 3
9 6 2002 2005 1
10 5 2002 2005 1
11 6 2002 2002 -
12 6 2002 2005 2

------------------------------------------------------

Tbl3_Term

Tbl3_ID TermID Value
------- ------ -----
1 1 12
1 2 14
2 1 10k
2 3 17
3 2 22

Any help will be greatly appreciated.

Thank you.
PKS.
[/code]

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-17 : 18:26:04

this is for increment updateing

declare @counter int
set @counter = 1
UPDATE Tbl1_ID
SET @counter = OrderNo = @counter + 1
Where deptid='z'

you will need to play with this to suit your needs.
if nothing else, write multiple updates for each table.
Cursors are a big NO, NO.

Go with the flow & have fun! Else fight the flow

sahu74
Posting Yak Master

100 Posts

Posted - 2004-09-20 : 10:28:34

Thank you so much Spirit. I made some changes to suit my needs and it works great. I would appreciate if you could explain how the following code executes


SET @counter = OrderNo = @counter + 1

Thank you.
PKS.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-20 : 10:47:44

i'll let our chief crack dealer (graz) do the explaining

http://www.sqlteam.com/item.asp?ItemID=765

Go with the flow & have fun! Else fight the flow

sahu74
Posting Yak Master

100 Posts

Posted - 2004-09-20 : 18:56:02

Awesome. This is one of the neatest thing I have seen in SQL so far. Thank you Spirit for the guidance and Graz for the discovery.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources