Update table using select results

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)

Update table using select results

Author

Topic

Pumkin
Starting Member

20 Posts

Posted - 2005-07-19 : 08:41:26

Hello
I'm kind of new in sql and i need an update statement that will use the results of a select statement. I have done something but my select returns more rows than one
My code is here
i need to update every field Sum_W in table Servers with the result given by the select. The sum_W Field has a corresponding IP

UPDATE Servers SET Sum_W=(SELECT sum(Response) AS Sumw FROM Statistica GROUP BY IP)

Thank you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-19 : 08:44:48

What is the relation between these two tables?
Post the table structures

Madhivanan

Failing to plan is Planning to fail

Pumkin
Starting Member

20 Posts

Posted - 2005-07-19 : 08:46:44

Statistica: Fields: Response,Ip
Servers: Fields: Ip, Sum_W

Pumkin
Starting Member

20 Posts

Posted - 2005-07-19 : 08:50:26

I need to get the sum of Response for each Ip and put it in the Sum_W in table Servers at the right Ip

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-19 : 08:58:38

UPDATE S SET S.Sum_W=(SELECT sum(Response) AS Sumw FROM Statistica
where ip=S.ip GROUP BY IP) from Servers S

Madhivanan

Failing to plan is Planning to fail

Pumkin
Starting Member

20 Posts

Posted - 2005-07-19 : 09:01:56

Thank You very much
It is working!! :)

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-19 : 16:03:05

There is no need for group by:

UPDATE Svr 
  SET Svr.Sum_W=(SELECT sum(st.Response) 
                   FROM Statistica st
                   where st.ip=Svr.ip) 
  from Servers Svr

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-20 : 00:51:45

Thats good point mmarovic

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources