Update Using a cursor

cursors

I know that instead of cursor we can have the tables updated with different queries but I wanted to try using cursor so as to have a knowledge on cursor
This was what I was trying I have inserted a new column which has null values instead of null I wanted to update that column based on the title column here what I used the query but I am unable to update the table can someone suggest a solution for this.

Declare @Title varchar(max)
Declare @Department varchar(max)

Declare Department_Cursor Cursor 
For 
    Select Title, Department 
    from Sample_table 

Open Department_Cursor
Fetch Next From Department_Cursor Into @Title, @Department

While @@fetch_status = 0
Begin
     Update sample_table
     Set @Department = 'Production' 
     where @Title = '%Production%'

     Fetch Next From Department_Cursor Into @Title, @Department
End

Close Department_Cursor 
Deallocate Department_Cursor

Best Answer

I tried running what you have and noticed one small error:

where @Title = '%Production%'

So if @Title ever actually = '%Production%' it will update the entire table. Until then it's going to update 0 rows. My guess is what you are trying to do is something like this:

WHERE @Title LIKE '%Production%'
  AND Title = @Title

And on further inspection I don't think you want this either

SET @Department = 'Production'

You probably mean

SET Department = 'Production'

Which also means you don't need to be pulling Department at all in your cursor. Last but not least, since you are updating the table that the cursor is pulling from I would use a READ ONLY cursor. This does however write the cursor results to tempdb so it may not be such a good idea if you have a large table.

Last comment: Cursors are not always bad. But they should only be used where batch processing is not possible. Say executing a stored procedure with multiple inputs. I think it's great that you are being proactive and practicing them however.

Related Question