cursor on update statement - 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 2008 Forums

Transact-SQL (2008)

cursor on update statement

Author

Topic

fatih252
Starting Member

4 Posts

Posted - 2012-05-09 : 09:12:07

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:


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:

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 ?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-09 : 09:53:31

quote:
Originally posted by fatih252

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:

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:
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 ?

This is because your update statement updates EVERY row in the tbl_app_monitoring_archive table. I assume you want to update specific rows in that table. If that is the case, how are tbl_app_monitoring_archive and tbl_app_monitoring related? You need to select that key in the cursor and add a where clause in the update statement using that key.

If you can post the table schema and some sample data, someone on the forum can write a query that can do this without using cursors.

fatih252
Starting Member

4 Posts

Posted - 2012-05-09 : 10:57:17

thank you for your answer,

this is the relation

first I inserted average and application_id and then update statement.

this is the archive table:

insert query:

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:

select cast(avg(cast(reply as decimal(12,2))) as decimal(12,2)) from tbl_app_monitoring
group by test_name, application_id

result:

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-09 : 11:28:27

Unfortunately I can't view the images from where I am. Hopefully someone else on the forum will respond with suggestions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources