>>Then what is the difference between for update of sal and for update statement?
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;
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;
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;
I hope now it is clear to you.
Regards
Krishna