Updating multi rows! - 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 multi rows!

Author

Topic

Feduz
Starting Member

4 Posts

Posted - 2006-01-23 : 19:18:09

Hi everyone,

Here is my problem ive a table with Two columns the 1st is "Ranks" and the 2nd is "Budget", now what i need is to update (in one update statement) the "Ranks" column and infront of the Highest number in the "Budget" column to put No. 1 and the 2nd highest No. 2, for example:

Ranks Budget
---------------
2 | 1522
1 | 2500
3 | 1050
4 | 250
---------------

What i need is when ever i change a number in the "Budget" i need to run an update statment to update the "Ranks", so the highest number will have rank 1 and so on...

Any help would be appreciated...

Thanks inadvance..

Feduz

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-23 : 20:07:25

Use the following to get the data in a select query
U can make use of the same to Update the table

SELECT  COUNT(t1.Budget) as [Rank], t1.Budget
FROM urTbl AS t1
INNER JOIN 
urTbl AS t2 ON t1.Budget >= t2.Budget
group by t1.Budget 
order by t1.Budget

Feduz
Starting Member

4 Posts

Posted - 2006-01-23 : 20:36:30

Perfect exactly what i wanted, but im sorry for being noobish, ive tried to put it in an update query but i couldnt know how?!

EDIT:
I came out with this:

Update urtbl
set rank = (SELECT COUNT(t1.budget) as [record #] , t1.budget
FROM urtbl AS t1
INNER JOIN
urtbl AS t2 ON t1.budget <= t2.budget
group by t1.budget
order by t1.bedget desc)
from urtbl

but it got error...

Feduz

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-23 : 20:48:08

Try the following:

Update urTbl t3 set Rank =  
   ( SELECT  COUNT(t1.Budget) 
     FROM urTbl AS t1
     INNER JOIN 
     urTbl AS t2 ON t1.Budget >= t2.Budget
     group by t1.Budget 
     order by t1.Budget ) as t4
Where t3.Budget = t4.Budget

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-23 : 20:50:33

this will not work if you have same Budget value in diff records

update  t
	set	ranks	= (select count(*) from urTbl x where x.budget >= t.budget)
	from	urTbl t

quote:
What i need is when ever i change a number in the "Budget" i need to run an update statment to update the "Ranks", so the highest number will have rank 1 and so on...

You don't have to update the "Ranks". Just

select Budget from urTbl order by Budget desc

and use your front end apps to do the ranking

----------------------------------
'KH'
I do work from home but I don't do homework

Feduz
Starting Member

4 Posts

Posted - 2006-01-23 : 21:03:18

quote:
You don't have to update the "Ranks".

I know but if i wanted to have for example budget rank 3 it would be easier to get..

By the way thanks it worked perfectly...

Feduz

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-24 : 01:04:10

>>and use your front end apps to do the ranking

Thats the effecient way

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-24 : 19:37:38

quote:
when ever i change a number in the "Budget" i need to run an update statment to update the "Ranks"

it is fine if the table is pretty static. If the table is large and Budget changes frequently, the Update might be costly.

Also take note of limitation of the method

quote:
this will not work if you have same Budget value in diff records

----------------------------------
'KH'
I do work from home but I don't do homework

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources