For update of

What is the difference between for update of columnname and for update?

select * From emp
for update of sal;

Here it locks entire table.

update emp
set job ='SALESMAN'
where empno=7369;

Eventhough I am updating job from another session it is blocking. Then what is use of specifying for update of sal?

The following statement also doing the same.
select * From emp for update ;

Then what is the difference between for update of sal and for update statement?

Which database u r using here. All doesn't support this.

[B]>>Then what is the difference between for update of sal and for update statement?[/B]

When you are working with a single table you will not find any difference. Select for update of locks rows not columns.

Do the following exercise then it will be clear to you.

From first session execute the following statemet.
[CODE]
SELECT a.ename,a.job,a.sal,b.dname
FROM emp a, dept b
WHERE a.deptno = b.deptno
FOR UPDATE OF sal;
[/CODE]
UPDATE of Columname lock only rows from tables which have a column listed in the OF clause. Rows are not locked in tables that don't have a column appearing in the OF clause.

Here sal is of table emp. It locks all the rows in EMP table only not DEPT table.You can update rows in DEPT table without any problem.

[CODE]
UPDATE dept
SET dname = 'EDP'
WHERE deptno = 40;
[/CODE]
But it will not allowing you to update emp until the first session release lock by applying commit or rollback command.

[CODE]
UPDATE emp
SET sal = 5000
WHERE empno = 7369;
[/CODE]
I hope now it is clear to you.


Regards
Krishna

When do we use where current of statement?

Where current of is used to update or delete records that have been referenced by select for update without using external reference.

Example:-
[CODE]
CREATE OR REPLACE PROCEDURE test_proc AS

CURSOR emp_cur IS
SELECT rowid,a.*
FROM emp a
FOR UPDATE ;

BEGIN

FOR v_cur IN emp_cur
LOOP
-- 1st method
UPDATE emp
SET sal = sal * 1.10
WHERE [B]empno = v_cur.empno[/B];

-- 2nd method
UPDATE emp
SET sal = sal * 1.10
WHERE [B]rowid = v_cur.rowid[/B];

-- 3rd method
UPDATE emp
SET sal = sal * 1.10
WHERE [B]current of emp_cur[/B];

END LOOP;

COMMIT;

END;
[/CODE]

Can't we use where current of without using for update?

No we can't.

It will show error
PLS-00404: cursor 'CURSOR NAME ' must be declared with FOR UPDATE to use with CURRENT OF