Select Update - 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 2005 Forums

Transact-SQL (2005)

Select Update

Author

Topic

cable_si
Starting Member

20 Posts

Posted - 2011-11-09 : 11:10:40

Hi

i am having problems with a query i have

i am trying to do a select update

i want to have something like

update my_table
set dur = (SELECT
@rec_count = (select count(*) from my_table),
@sum_dur = (select sum(dur) from my_table),
@averagedur = (@sum_dur / @rec_count))

so dur is = @averagedur

the problem is my select statement is a bit off, as i am returning 3 values when i only want one
hope that makes sense

thanks

Simon

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 11:27:01

[code]update my_table
set dur = (select count(*) from my_table)*1.0/(select sum(dur) from my_table)
[/code]

are you aware that this will set same value for Dur field in all rows?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cable_si
Starting Member

20 Posts

Posted - 2011-11-09 : 11:37:25

quote:
Originally posted by visakh16

are you aware that this will set same value for Dur field in all rows?

i am yes thanks

intrestingly when i add further constraints to the query the result becomes 0,

ie.

select count(*) from my_table where field1 = 'Hello')*1.0/(select sum(dur) from my_table where field1 = 'Hello'

any ideas

thanks

Simon

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 11:39:22

whats the datatype of dur?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cable_si
Starting Member

20 Posts

Posted - 2011-11-09 : 11:40:34

quote:
Originally posted by visakh16

whats the datatype of dur?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hi

its an int

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 12:43:13

quote:
Originally posted by cable_si

quote:
Originally posted by visakh16

whats the datatype of dur?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hi

its an int

thanks

then it wont be able to store decimal result
either make it decimal/numeric or apply cieling to round it to nearest integer


update my_table
set dur = cieling( (select count(*) from my_table)*1.0/(select sum(dur) from my_table))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources