Need help wiht an Update Corsor

[cal555]

Hi I am trying to get an update cursor to work it is having a problem at the IF statements below I know the I am not writing the code right there, but I do not know how else t put it. basicaly, I want it to put a 2 in the [topic_image_type] field, for any record, that has a 0 in the [h_topic]field, and put a 4 in the [topic_image_type] field, for any record, that has a 1 in the [h_topic]field. It comes back with an error saying the Subquery returned more than 1 value.
If nayone can help me with this I would Appreciate it.
Thank You

Here is my code:

CREATE PROCEDURE Image_Type
AS

DECLARE @intErrorCode INT

Declare upc Cursor
Local
Keyset
For Select t.[topic_image_type], t.[h_topic] From topic t, relationship r Where t.[topic_guid] = r.[topic_guid] And r.[rel_id] = 2

For Update

Open upc

Fetch upc

Begin Tran

IF (Select t.[h_topic] From topic t, rel r Where t.[topic_id] = r.[topic_id] And r.[rel_id] = 2 And [h_topic] = 0) = 0
Begin
Update topic
Set [topic_image_type] = 2
Where Current OF upc
End

IF (@@ERROR <> 0 ) GOTO ErrorHandler

IF (Select t.[h_topic] From topic t, relationship r Where t.[topic_id] = r.[topic_id] And r.[rel_id] = 2 And [h_topic] = 1) = 1
Begin
Update topic
Set [topic_image_type] = 4
Where Current OF upc
End

COMMIT TRAN

RETURN 0

Close upc
Deallocate upc

ErrorHandler:
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
RETURN 1
GO

 

[SQLSister]

Your first problem is that this is something that should not be done in a cursor. Cursors are to be avoided at all costs when updating tables or inserting to them or deleting from them. They are terribly inefficient and get much worse performance of work can take hours or even days where a set-based update may take milliseconds or minutes.

Use one update statement with a case statement inthe set statement or two update staments withthe condtions inthe where clause. Look up Case in BOL.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[cal555]

Thanks I am aware of this, but in testing this cursor I found that it only takes a few milliseconds because it is only on about 200 rows. And while you are right I would like to know what I am doing wrong in this cursor since worte the the whole thing this way and ti seems mto be just one problem away form working. So can you tell me what is worng with that line of code.
Thank you