Updating multiple tables - SQL Server Forums

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)

Updating multiple tables

Author

Topic

sahu74
Posting Yak Master

100 Posts

Posted - 2004-09-15 : 16:24:58

I have 5 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)

Term( TermID, TermName, YearNo)

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


Tbl1( Tbl1_ID, DeptID, StartYear, EndYear, OrderNo)

Tbl2( Tbl2_ID, Tbl1_ID, StartYear, EndYear, OrderNo)

Tbl3( Tbl3_ID, Tbl2_ID, StartYear, EndYear, OrderNo)

Tbl3_Term (Tbl3_ID, TermID, Value)

Term( TermID, TermName, YearNo)

I want to update the new column in each table based on the DeptID. For all the records associated with a DeptID in Tbl1, I want the OrderNo to be updated as 1, 2, 3, 4,...For the next DeptID 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 am not sure how to update this. Any help will be appreciated.

Thank you.
PKS.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-15 : 19:11:29

Not sure that I've fully understood exactly what you're trying to do. Can you supply some sample data to explain? About 10 or so rows should suffice

sahu74
Posting Yak Master

100 Posts

Posted - 2004-09-15 : 20:58:27

Here is some sample data. Hope this will help. I will be glad to explain this data more if needed.


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		2005

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

Tbl3

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

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

Tbl3_Term

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


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

Term

TermID		TermName	YearNo
------		--------	------	
1		TermA		2001
2		TermB		2001
3		TermA		2002
4		TermB		2002

******************************************************
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		2005		2

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

Tbl3

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

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

Tbl3_Term

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


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

Term

TermID		TermName	YearNo
------		--------	------	
1		TermA		2001
2		TermB		2001
3		TermA		2002
4		TermB		2002

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-15 : 21:24:40

I may be missing something, but it doesn't seem that the Tbl3_Term and Term tables are being used at all.

sahu74
Posting Yak Master

100 Posts

Posted - 2004-09-16 : 00:46:31

I might not need the Term and Tbl3_Term table for this update. Please ignore them. Sorry if this caused some confusion.

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

Thank you.
PKS.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-16 : 01:18:35

first of all, you could probably establish a relationship between tbl1 and tbl2, tbl2 and tbl3,
so if you update tbl1_id and tbl2_id, you can cascade the update to tbl2 referencing to tbl1 via tbl1_id,
and tbl3 referencing to tbl2 via tbl2_id.

then when you update tbl1, no need to worry if tbl_id on tbl2 was updated, also on tbl2 for tbl3

so now you're problem is how to update tbl1 and tbl2...
from your example,
1. you need to group the rows in tbl1 by similar DeptID and endyear < 2004,
2. then add a counter so that when you've updated 4 records, your orderno, returns to 1.

does that make sense?

have fun...

sahu74
Posting Yak Master

100 Posts

Posted - 2004-09-16 : 11:36:02

Thank you for the logic. I kind of understand it. Can I get some sample code for the same(using the counter)?

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources