update table using cursor - 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)

update table using cursor

Author

Topic

u2p_inst
Yak Posting Veteran

78 Posts

Posted - 2004-01-14 : 02:34:53

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-01-14 : 02:36:37

Why do you want to use a cursor ?????

Attitude is everything

{The Enigma}

u2p_inst
Yak Posting Veteran

78 Posts

Posted - 2004-01-14 : 02:53:00

the truth condition is not like that more complicated, but i realy need update the table using cursor, but i don't mind if you have any solution or idea

Thank's

oh

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-01-14 : 03:09:14

this would do the trick on the example you have given

update a
set value_b = value_a
from table_a, table_b
where name_a = name_b

u2p_inst
Yak Posting Veteran

78 Posts

Posted - 2004-01-14 : 03:42:10

thank's that working properly,
but have you solution using cursor?

oh

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-01-14 : 04:06:21

quote:
Originally posted by u2p_inst

thank's that working properly,
but have you solution using cursor?

Avoid TSQL cursors at all costs. They usually are slower and/or require more server resources than a join or subquery.

-ec

u2p_inst
Yak Posting Veteran

78 Posts

Posted - 2004-01-14 : 04:07:43

i need using cursor because the other condition like:
if the table_b is empty then i have to using "insert" not "update"
and combining with looping to insert all records in table_a

oh

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-01-14 : 04:21:47

quote:
Originally posted by u2p_inst

i need using cursor because the other condition like:
if the table_b is empty then i have to using "insert" not "update"
and combining with looping to insert all records in table_a

You really should think about not using cursors for stuff like this. Having a primary key on these tables would make it easier for you to first delete any rows that would need to be updated. That would eliminate the step where you need to determine if you are performing an update or an insert. You would just do an insert every time.

Anyway, if you are still deadset on the cursor idea, this should get you started. Completely untested code follows:

-- Declare some variables to use
DECLARE @value_a varchar(20), @name_a varchar(20)

-- Declare our cursor
DECLARE u2p_inst_cursor CURSOR FOR
SELECT name_a, value_a FROM table_a

OPEN u2p_inst_cursor 

-- Perform the first fetch.
FETCH NEXT FROM u2p_inst_cursor 
INTO @name_a, @value_a

-- Perform first update
UPDATE table_b
SET value_b = @value_a
WHERE name_b = @name_a

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM authors_cursor
   INTO @name_a, @value_a

   -- Perform Updates in loop
   UPDATE table_b
   SET value_b = @value_a
   WHERE name_b = @name_a
END

CLOSE u2p_inst_cursor 
DEALLOCATE u2p_inst_cursor 
GO

There are more examples similar to this in BOL.

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources