Cursor problem. - 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 2005 Forums

Transact-SQL (2005)

Cursor problem.

Author

Topic

hdv212
Posting Yak Master

140 Posts

Posted - 2008-08-12 : 17:37:02

Hi
i have two tables as like this :
tbl1 :

id uniqueidentifier pk
parvande nvarchar(20) unique

tbl2 :

id uniqueidentifier pk
parvande nvarchar(20) unique
tbl1ID uniqueidentifier Fk

the Third column in tbl2 (tbl1ID) add after table created and some values enter to it, now tbl1ID is empty column for each record.

for solve this problem i want to use cursor to iterate each record in tbl1, get tbl1.id,parvande, and update tbl2 (set tbl2.tbl1ID where parvande=tbl1.parvande) like this :

declare cur1 cursor
LOCAL
FORWARD_ONLY
READ_ONLY
for select id,parvande from tbl1

open cur1
declare @id uniqueidentifier
declare @parvande nvarchar(20)
fetch next from cur1 into @id,@parvande
while @@fetch_status = 0
begin
	update tbl2 set tbl1ID = @id where parvande = @parvande
	fetch next from cur1 into @id,@parvande
end
close cur1
deallocate cur1

but after update first record, the following error message display to me :

Could not complete cursor operation because the table schema changed after the cursor was declared.

how to solve my problem ?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-12 : 17:49:37

I think you don't need a cursor here.
Try this:
update t2
set tbl1ID = t1.id
from tbl2 t2
inner join tbl1 t1 on t2.parvande = t1.parvande

Webfred

Too Old to Rock 'n' Roll, Too Young to Die

hdv212
Posting Yak Master

140 Posts

Posted - 2008-08-12 : 18:49:46

thanks. my problem was solved.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources