CODEWITHSUNDEEP
sql-servert-sql
Ayoub EL Khaddari
Ayoub EL Khaddari

Reputation: 47

T-SQL : understand cursor for update of

I don't know what the role of for update of is in a cursor in T-SQL.

I wrote this SQL statement:

declare @id int, @nom varchar(30), @age int;

declare cur_info cursor for 
     select id, nom, age 
     from info **FOR UPDATE OF** age;

open cur_info;

fetch next from cur_info into @id, @nom, @age;

while @@FETCH_STATUS = 0
begin
    if @age = 20
        update info 
        set age = 0 
        where id = @id;

    fetch next from cur_info into @id , @nom , @age;
end

close cur_info;
deallocate cur_info;

When I execute the code, the update was done but when I don't use for update of age the update also done I want to understand what mean "for update of" and if you can; I want a real example of using this

Upvotes: 0

Views: 3457

Answers (2)

Ayoub EL Khaddari
Ayoub EL Khaddari

Reputation: 47

I find the solution my problem is why when i write for update of age in the cursor I can update age and id , name..... example :

declare @id int,@name varchar(30),@age int;
declare cur_info cursor  for select id , nom , age from info  for update of age ;
open cur_info;
fetch next from cur_info into @id,@nom,@age;
while @@FETCH_STATUS=0
begin
if @age = 20
update info set age = 10 where id = @id ;
print @age;
fetch next from cur_info into @id , @nom , @age;
end
close cur_info;
deallocate cur_info;

but when I use the 'current of cursor_name' after where then I can't update any column exepte var1 and the sql server give me an error "The cursor has a FOR UPDATE list and the requested column to be updated is not in this list"

example

declare @id int,@nom varchar(30),@age int;
declare cur_info cursor  for select id , nom , age from info  for update of nom ;
open cur_info;
fetch next from cur_info into @id,@nom,@age;
while @@FETCH_STATUS=0
begin
if @age = 0
update info set age = 20 where current of cur_info ;
print @age;
fetch next from cur_info into @id , @nom , @age;
end
close cur_info;
deallocate cur_info;

Upvotes: 1

HABO
HABO

Reputation: 15852

From DECLARE CURSOR:

UPDATE [OF _column_name_ [,...n]] Defines updatable columns within the cursor. If OF [, <... n>] is specified, only the columns listed allow modifications. If UPDATE is specified without a column list, all columns can be updated.

A use case might be a table Users where you want the code using the cursor to be able to update EmailAddress and TelephoneNumber, but not Username, HashedPassword or RegistrationDate. ... for update of EmailAddres, TelephoneNumber ... would prevent changes to the other columns.

Upvotes: 0

Related Questions