please help to write update query

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

SQL Server Development (2000)

please help to write update query

Author

Topic

maven_9999
Starting Member

7 Posts

Posted - 2004-11-23 : 06:25:25

Hi,

I have a table with data like below.

IP - service - hit -rank1-rank2
1.1.1.1 - Udp - 400
1.1.1.1 - Tcp - 300
2.2.2.2 - Udp - 100
2.2.2.2 - Tcp - 150

by default rank1, rank2 will be null. Need to update ranks and resulted table as looks like....

IP - service - hit -rank1-rank2
1.1.1.1 - Udp - 400 - 1 - 1
1.1.1.1 - Tcp - 300 - 1 - 2
2.2.2.2 - Udp - 100 - 2 - 2
2.2.2.2 - Tcp - 150 - 2 - 1

rank1 should be calculated like ....
sum(hit) from table group by ip.

and rank2 should be calculated like...

sum(hit) from table group by service where ip=ip.

Rank1 will be max hits to one particular ip, no matter whatever service is.

Rank2 will be max of hits to one particular service, for one ip.

Please help me to write update query for above. Its little urgent for me.

Thanks.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-23 : 06:27:03

duplicate post

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=42840

--------------------
keeping it simple...

maven_9999
Starting Member

7 Posts

Posted - 2004-11-23 : 06:54:52

Hi,

I am looking for an update statement. As I tried with creating temporary tables as you said in previous reply. But performance is too poor when I am working with large DB. I can able to get Rank1 with very good speed with below query.

create table ranking1
( rank1 integer not null primary key auto_increment
, ip varchar(15)
, hit integer
);
insert into ranking1 (ip, hit) select ip, sum(hit) as h from table1 group by ip order by h desc limit 100;

insert into table2 (user_ip,service,hit,rank1)
select mtbservicein.ip
, mtbservicein.protoid
, mtbservicein.hit
, ranking1.rank1
from table1
inner
join ranking1
on table1.ip
= ranking1.ip
order
by ranking1.rank1;

But when I am trying to insert Rank2, its takign toomuch time. So thought to perform an update statement to resulted table.
Please help me to get an update query for above.
I really appreciate your help.

Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-23 : 08:32:19

Please do not cross post, and continue the discussion in the original thread linked above.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources