For update of

**babi_geek** · 06-05-2008

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?

**arindam_0006in** · 06-05-2008

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

**krishnaindia2007** · 06-06-2008

>>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

**babi_geek** · 06-06-2008

When do we use where current of statement?

**krishnaindia2007** · 06-06-2008

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   empno = v_cur.empno;

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

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

END LOOP;

COMMIT;

END;

**babi_geek** · 06-06-2008

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

**krishnaindia2007** · 06-06-2008

No we can't.

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

Thread: For update of

Thread Tools

Display

For update of

Re: For update of

Re: For update of

Re: For update of

Re: For update of

Re: For update of

Re: For update of

Posting Permissions

About us

GeekInterview

Interact