select update

  • 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

  • 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/

  • thanks, but that just returns 0 as the result

    any more ideas

    thanks

    simon

  • 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/

  • 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

  • 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