select update

• Simon Parry

  Ten Centuries

  Points: 1297

  More actions

  November 9, 2011 at 9:11 am

  #390813

  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

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  November 9, 2011 at 9:21 am

  #1405304

  Simon Parry (11/9/2011)

  ---

  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

  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.

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• Simon Parry

  Ten Centuries

  Points: 1297

  More actions

  November 9, 2011 at 9:30 am

  #1405313

  thanks, but that just returns 0 as the result

  any more ideas

  thanks

  simon

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  November 9, 2011 at 9:38 am

  #1405325

  Simon Parry (11/9/2011)

  ---

  thanks, but that just returns 0 as the result

  any 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.

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• sami.sqldba

  Ten Centuries

  Points: 1248

  More actions

  November 18, 2011 at 9:35 am

  #1410026

  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

• Sam Bendayan

  SSC-Addicted

  Points: 479

  More actions

  November 21, 2011 at 9:24 pm

  #1411188

  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