Results 1 to 7 of 7

Thread: For update of

  1. #1
    Contributing Member
    Join Date
    Mar 2008
    Answers
    66

    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?


  2. #2
    Junior Member
    Join Date
    Aug 2007
    Answers
    9

    Re: For update of

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


  3. #3
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: For update of

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

    Last edited by krishnaindia2007; 06-06-2008 at 02:43 AM.

  4. #4
    Contributing Member
    Join Date
    Mar 2008
    Answers
    66

    Re: For update of

    When do we use where current of statement?


  5. #5
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: For update of

    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;



  6. #6
    Contributing Member
    Join Date
    Mar 2008
    Answers
    66

    Re: For update of

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


  7. #7
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: For update of

    No we can't.

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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact