Results 1 to 6 of 6
  1. #1
    Join Date
    May 2012
    Posts
    3

    Unanswered: using cursor for update statement

    hello

    I would like to get the results of a cursor into update statement but it fills only the last record of the cursor

    this is the cursor:


    Code:
    DECLARE @avg varchar(50)
    DECLARE @cur_avg CURSOR 
    SET @cur_avg = CURSOR FOR
    select cast(avg(cast(reply as decimal(12,2))) as decimal(12,2)) from tbl_app_monitoring
    group by test_name, application_id


    this is the update statement:

    Code:
    OPEN @cur_avg
    FETCH @cur_avg INTO @avg
    
    WHILE (@@FETCH_STATUS = 0) BEGIN
    
    UPDATE tbl_app_monitoring_archive
    SET average  = @avg
    
    FETCH @cur_avg INTO @avg
    END

    is it also possible to do this without the cursor ?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are you really trying to update every record in tbl_app_monitoring_archive to the same value?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    The relationship between the tables and tbl_app_monitoring tbl_app_monitoring_archive is done through what columns?

    With cursor you can try something like this:

    Code:
    OPEN @cur_avg;
    FETCH NEXT FROM @cur_avg INTO @avg;
    
    WHILE (@@FETCH_STATUS = 0) 
      BEGIN
        UPDATE tbl_app_monitoring_archive
                SET average  = @avg
        FETCH NEXT FROM @cur_avg INTO @avg;
      END;
    CLOSE @cur_avg;
    DEALLOCATE @cur_avg;
    Hope this helps.

  4. #4
    Join Date
    May 2012
    Posts
    3
    Quote Originally Posted by imex View Post
    Hi,

    The relationship between the tables and tbl_app_monitoring tbl_app_monitoring_archive is done through what columns?

    With cursor you can try something like this:

    Code:
    OPEN @cur_avg;
    FETCH NEXT FROM @cur_avg INTO @avg;
    
    WHILE (@@FETCH_STATUS = 0) 
      BEGIN
        UPDATE tbl_app_monitoring_archive
                SET average  = @avg
        FETCH NEXT FROM @cur_avg INTO @avg;
      END;
    CLOSE @cur_avg;
    DEALLOCATE @cur_avg;
    Hope this helps.

    thank you for your answer,

    this is the relation
    Click image for larger version. 

Name:	Naamloos.png 
Views:	2 
Size:	18.3 KB 
ID:	13109

    first I inserted average and application_id and then update statement.

    this is the archive table:
    Click image for larger version. 

Name:	archive.png 
Views:	1 
Size:	6.5 KB 
ID:	13107

    insert query:
    Code:
    select application_id, test_name,status,count(*) as number
    into #temptb
    from tbl_app_monitoring
    group by application_id, test_name, status
    
    select application_id , test_name,sum(number) as total
    into #temptb2
    from #temptb
    group by application_id, test_name
    
    insert into tbl_app_monitoring_archive (test_name,percentage_status,application_id)
    select TT1.test_name, cast(((number*1.0) /(total*1.0))*100 as int),TT1.application_id   from 
    #temptb TT1 inner join #temptb2 TT2
    on TT1.application_id = TT2.application_id
    where status = 'alive'
    group by TT1.test_name, number , total,TT1.application_id

    this is the query that i want to update:
    Code:
    select cast(avg(cast(reply as decimal(12,2))) as decimal(12,2)) from tbl_app_monitoring
    group by test_name, application_id
    result:
    Click image for larger version. 

Name:	avg.png 
Views:	1 
Size:	2.4 KB 
ID:	13108

  5. #5
    Join Date
    Apr 2012
    Posts
    213
    I do not know if I understand correctly but try this:

    Code:
    with CTE as
    (
        select 
            test_name,
            application_id,
            cast(avg(cast(reply as decimal(12,2))) as decimal(12,2)) as AVG_Reply
        from tbl_app_monitoring
        group by test_name, application_id
    )
    
    UPDATE tbl_app_monitoring_archive
    SET average = c.AVG_Reply
    from tbl_app_monitoring_archive as t
    join CTE as c on (c.test_name = t.test_name) and 
                     (c.application_id = t.application_id)
    Hope this helps.

  6. #6
    Join Date
    May 2012
    Posts
    3
    Quote Originally Posted by imex View Post
    I do not know if I understand correctly but try this:

    Code:
    with CTE as
    (
        select 
            test_name,
            application_id,
            cast(avg(cast(reply as decimal(12,2))) as decimal(12,2)) as AVG_Reply
        from tbl_app_monitoring
        group by test_name, application_id
    )
    
    UPDATE tbl_app_monitoring_archive
    SET average = c.AVG_Reply
    from tbl_app_monitoring_archive as t
    join CTE as c on (c.test_name = t.test_name) and 
                     (c.application_id = t.application_id)
    Hope this helps.

    yes! this just what i needed, thank you for your time

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •