cursor update 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 2000 Forums

Transact-SQL (2000)

cursor update problem

Author

Topic

asarak
Starting Member

36 Posts

Posted - 2005-08-24 : 08:28:05

Hi guys again,
i ve got a problem with a cursor
i want to update the onoma field with the onoma_new field
where onoma is empty (not null) and onoma_new is not empty.
Is the if statement ok? to do this???

tmp_table

code_pela eponimo onoma onoma_new
150001 ALPHA ALPHA
102008 BETTA BETTA
102010 GAMMA GAMMA
102013 DELTA DELTA
998854 TEST TEST

declare @eponimo char(50),@onoma char(20),@code_pela int, @onoma_new char(20)
declare cursor1 cursor for
select code_pela,eponimo,onoma,onoma_new
from tmp_table
open cursor1
fetch next from cursor1
into @code_pela,@eponimo,@onoma,@onoma_new
while (@@fetch_status <> -1)
begin
fetch next from cursor1 into @code_pela,@eponimo,@onoma,@onoma_new
select @code_pela,@eponimo,@onoma,@onoma_new
where @onoma = ' ' and @onoma_new <> ' '
-- if @onoma = ' ' and @onoma_new <> ' '
-- update tmp_pelates_2 set onoma=@onoma_new where code_pela=@code_pela
end
close cursor1
deallocate cursor1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-24 : 08:57:13

Why do you use cursor for this?
Try this
(before that take backup of that table and updation is wrong restore it)

Update yourTable set onama=onama_new where onama='' and onama_new<>''

Madhivanan

Failing to plan is Planning to fail

asarak
Starting Member

36 Posts

Posted - 2005-08-25 : 08:18:08

I found it finally!!!!!!!

Here it is. The problem were found in the (if) statement but with "trim" works fine.
I also use print to check it but for 64000 rows works too slow.
Is there any other way more efficient???
Thanks a lot anyway.

ASARAK

declare @eponimo char(50),@onoma char(20),@code_pela int, @onoma_new char(20)
declare cursor1 cursor for
select code_pela,eponimo,onoma,onoma_new
from tmp_pelates_2
open cursor1
fetch next from cursor1
into @code_pela,@eponimo,@onoma,@onoma_new
while (@@fetch_status =0)
begin
if ltrim(rtrim(@onoma)) = '' and ltrim(rtrim(@onoma_new)) <> ''
begin
update tmp_pelates_2 set onoma=@onoma_new where @code_pela=code_pela
print cast((@code_pela) as varchar(10))
+ ' ' + @eponimo
+ ' ' + @onoma
+ ' ' +'NEED FOR UPDATE'
+ ' ' + @onoma_new
end
else
begin
print cast((@code_pela)as varchar(10))
+ ' ' + @eponimo
+ ' ' + @onoma
+ ' ' +'NO UPDATE NEEDED'
end
fetch next from cursor1 into @code_pela,@eponimo,@onoma,@onoma_new
end
close cursor1
deallocate cursor1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-25 : 08:53:41

Cant you do this without using Cursor as I specified?

Madhivanan

Failing to plan is Planning to fail

asarak
Starting Member

36 Posts

Posted - 2005-08-25 : 08:57:54

i could not find out how...
could you please help me more???
thanks a lot
ASARAK

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-25 : 09:00:02

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-08-25 : 09:31:49

Did you see the UPDATE statement that Madhivanan posted for you? That's the answer, or at least using the UPDATE statementin general is the efficient way to do this. Are you familiar with UPDATE? Look it up in books on-line for more info. Experiment with it on some dummy tables to get a feel for it, since it can quickly mess up your tables as Madhivanan mentioned.

asarak
Starting Member

36 Posts

Posted - 2005-08-25 : 10:06:02

Of course i saw Madhivanan post, and its ok and simple but i just wonder if the cursor itself could be more efficient

thanks a lot guys
RGRDS
ASARAK

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-25 : 10:12:08

quote:
Originally posted by asarak

Of course i saw Madhivanan post, and its ok and simple but i just wonder if the cursor itself could be more efficient

thanks a lot guys
RGRDS
ASARAK

Cursors are not more efficient when it can be done without cursors

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources