January 18, 2007 at 10:06 am
I have the following that I would like to be able to use to update the routesteptasks table. The problem is using this cursor it takes forever to complete. I have read a few articles on the site that state that you should only use a cursor as a last resort. Is it possible to accomplish the update I am looking for without using a cursor? and if so can someone please point me in the right direction?
Thanks in advance for your help.
Declare @RSTID as int, @PID as int, @plugID as int, @PLugName as varchar(25)
Declare DaCursor cursor for
Select
b.Routesteptaskid,
e.ProjectID ,
f.pluginid,
f.pluginname from routesteps
as a inner join Routesteptasks as b on a.Routestepid = b.Routestepid
inner join Routes as c on a.RouteID = c.RouteID
inner join users as d on c.Routename = d.Contact
inner join Projects as e on d.Username = e.Projectname
inner join plugins as f on e.projectid = f.Projectid
Open DaCursor
Fetch Next from DaCursor into @RSTID, @PID, @plugID, @PlugName
While @@Fetch_Status = 0
Begin
Update Routesteptasks set Actiontype = 4, ActionNum = @plugID where RoutesteptaskID = @RSTID
Fetch Next from DaCursor into @RSTID, @PID, @plugID, @PlugName
End
Close DaCursor
Deallocate DaCursor
January 18, 2007 at 10:20 am
Fairly basic stuff, this... I would recommend you spend some time reading about T-SQL programming.
UPDATE b
SET Actiontype = 4,
ActionNum = f.pluginid
FROM Routesteps a
JOIN Routesteptasks b
ON a.Routestepid = b.Routestepid
JOIN Routes c
ON a.RouteID = c.RouteID
JOIN users d
ON c.Routename = d.Contact
JOIN Projects e
ON d.Username = e.Projectname
JOIN plugins f
ON e.projectid = f.Projectid
Another tip: you'll probably find it easier when you review your code in a few months' time if you use meaningful aliases for your tables rather than just a,b,c,d,e,f.
John
January 19, 2007 at 3:33 am
there's been an article by Andy Warren with a mass of posts on the subject of cursors. Let's not start another < grin >
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply