November 9, 2011 at 9:11 am
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
November 9, 2011 at 9:21 am
Simon Parry (11/9/2011)
Hii 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
UPDATE my_table
SET dur = (SELECT COUNT(*) / SUM(dur)
FROM my_table)
*WARNING*
The above update statement will update EVERY row in the table to the "average", so they will all contain the same data. You will lose the original values.
--EDIT--
Also, if "dur" is an INT column, then you'll need to CONVERT it to make the answer retain decimals.
November 9, 2011 at 9:30 am
thanks, but that just returns 0 as the result
any more ideas
thanks
simon
November 9, 2011 at 9:38 am
Simon Parry (11/9/2011)
thanks, but that just returns 0 as the resultany more ideas
thanks
simon
It returns 0 because "dur" is an integer. So as I said, you'll need to CONVERT it.
e.g.
UPDATE my_table
SET dur = (SELECT COUNT(*) / (SUM(dur) * 1.00000)
FROM my_table)
Of course, this means that your update statement will always stick 0 in there, because decimals can't be inserted into an INT column.
November 18, 2011 at 9:35 am
Hi Simon
Hope you have got the solution from the above comment but respect to your query i just want to make you aware
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))
This query innner select trying to merge the value of sum_dur and rec_count and result into averagedur you have just forget that first two value are getting into same select statment so you cant get one value for it bec you 3value are coming together with single select statment hope i have cleard you.
Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com
November 21, 2011 at 9:24 pm
There are several problems here. First, we don't have the structure of my_table. I'm assuming in the code below that it includes 3 columns and that you want to update them with the 3 variables you posted.
Second, you created a subquery to update only one of the columns, but the subquery itself is returning 3 columns. It can only return one column since you are using it to update a column in the table. And actually it can only return a single, scalar value, meaning one column and one row only. Otherwise it will error out.
Third, the subquery is populating variables. You can't do this if the subquery is intended to update a column in a table directly. Your subquery would end up populating the variables instead of returning the data to the UPDATE statement that calls it.
Fourth, in this case the subquery will update all the rows of the table with the same data...not sure if this is what you want. Because of this, you don't need to use a subquery within the UPDATE statement. You can use it outside of the statement to populate the 3 variables you posted and then use the 3 variables in your UPDATE statement directly. It will perform much better. If you use a subquery within the UPDATE statement then this subquery may end up running once for each row within your result set, which would perform very badly.
Here is the code that implements all the above suggestions. I made several assumptions which I hope are correct, but even if they aren't you should be able to get the gist of the ideas:
drop table my_table
create table my_table(dur int, col2 int, col3 int)
insert into my_table(dur)
select top 10000 row_number() over(order by name)
from sys.objects
DECLARE @rec_count int, @sum_dur int, @averagedur decimal(10, 2)
select @rec_count = count(*), @sum_dur = sum(dur) from my_table
set @averagedur = @sum_dur / @rec_count
update my_table
set dur = @rec_count,
col2 = @sum_dur,
col3 = @averagedur
select * from my_table
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply