Update Query Challenge - 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)

Update Query Challenge

Author

Topic

homerjay80
Starting Member

26 Posts

Posted - 2007-05-17 : 00:12:49

I need a blazing hot sql mind for this one. I would be greatful if someone could help me come up with an update script that can do the following.

We have a table with 3 columns. A will always be sequential.
With the exception of the A1, B will be the sum of the previous lines B and C.

A B C
1 1 1
2 2 5
3 7 3
4 10 1

What I need is a script that will adjust the B column when a change in C is made. So using this example, say if the value of C at A:2 changes to 10. The script would adjust the following rows but leave the previous unchanged and stop when their are no more rows.

A B C
1 1 1
2 2 10
3 12 3
4 15 1

Any takers? Let me know if you have any questions.
Thanks in advance for your help.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2007-05-17 : 00:26:49

Next time could you give us the T/SQL script for the problem..

This has a loop but should get you started...


declare @TestTable table (a int, b int, c int)

insert @TestTable 
Select 1, 1, 1
UNION ALL
Select 2, 2, 5
UNION ALL
Select 3, 7, 3
UNION ALL
Select 4, 10, 1

select * from @TestTable

update @TestTable set c = 10
where A = 2

while @@ROWCOUNT > 0
	update T set b = (Select x.b+x.c from @TestTable X where X.a = T.a-1)
	from @TestTable T
	where exists (Select 1 from @TestTable X where X.a = T.a-1 and T.b != X.c+X.b)

select * from @TestTable

DavidM

Production is just another testing cycle

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-17 : 00:40:02

i'm curious, what is the business case for such a table? seems like a poor design whatever it is.

any design where updating one value causes a veritable avalanche of updates elsewhere ought to be re-thunk.

www.elsasoft.org

homerjay80
Starting Member

26 Posts

Posted - 2007-05-17 : 01:41:44

The business case is I need a hack to update this table on the back end instead of using gui. I agree it the design(not mine) is flawed but it has to be flexible for any possible requirements.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources