1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

UPDATE Statements in SQL

Discussion in 'SQL PL/SQL' started by rajavu, Nov 14, 2008.

  1. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    UPDATE is another widely used DML in SQL . It is used to update the records with some values specified in the query. There are different scenarios for updation some of them are not yet fully explored .Hope this article will be useful for the SQL Developers especially for the newbies.

    Lets list the initial values in the table before we start updation.

    Code (Text):

    SQL> SELECT EMPNO,DEPTNO,ENAME, SAL FROM EMP;

         EMPNO     DEPTNO ENAME             SAL
    ---------- ---------- ---------- ----------
          7369         20 SMITH             800
          7499         30 ALLEN            1600
          7521         30 WARD             1250
          7566         20 JONES            2975
          7654         30 MARTIN           1250
          7698         30 BLAKE            2850
          7782         10 CLARK            2450
          7788         20 SCOTT            3000
          7839         10 KING             5000
          7844         30 TURNER           1500
          7876         20 ADAMS            1100
          7900         30 JAMES             950
          7902         20 FORD             3000
          7934         10 MILLER           1300

    14 rows selected.

    SQL> select * from DEPT_STAT;

        DEPTNO DNAME          LOC              AVG_SAL    MAX_SAL
    ---------- -------------- ------------- ---------- ----------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON

    SQL>
     
    Update All Records

    Here it updates the field in entire records with the value given.

    Code (Text):

    SQL> UPDATE  DEPT_STAT
      2  SET     AVG_SAL = 0;

    4 rows updated.

    SQL> select * from DEPT_STAT;

        DEPTNO DNAME          LOC              AVG_SAL    MAX_SAL
    ---------- -------------- ------------- ---------- ----------
            10 ACCOUNTING     NEW YORK               0
            20 RESEARCH       DALLAS                 0
            30 SALES          CHICAGO                0
            40 OPERATIONS     BOSTON                 0

    SQL> rollback;

    Rollback complete.

    SQL> select * from DEPT_STAT;

        DEPTNO DNAME          LOC              AVG_SAL    MAX_SAL
    ---------- -------------- ------------- ---------- ----------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON

    SQL> UPDATE  DEPT_STAT
      2  SET     AVG_SAL = 0,
      3          MAX_SAL = 0;

    4 rows updated.

    SQL> select * from DEPT_STAT;

        DEPTNO DNAME          LOC              AVG_SAL    MAX_SAL
    ---------- -------------- ------------- ---------- ----------
            10 ACCOUNTING     NEW YORK               0          0
            20 RESEARCH       DALLAS                 0          0
            30 SALES          CHICAGO                0          0
            40 OPERATIONS     BOSTON                 0          0

    SQL> rollback;

    Rollback complete.

    SQL>
     
    Update Specified Record

    Here it updates the columns of the restricted records with the value given in the query. Restriction is made possible by using WHERE Clause.

    Code (Text):

    SQL> UPDATE  DEPT_STAT
      2  SET     AVG_SAL = 0
      3  WHERE   DEPTNO  = 10;

    1 row updated.

    SQL> select * from DEPT_STAT;

        DEPTNO DNAME          LOC              AVG_SAL    MAX_SAL
    ---------- -------------- ------------- ---------- ----------
            10 ACCOUNTING     NEW YORK               0
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON

    SQL> rollback;

    Rollback complete.

    SQL> select * from DEPT_STAT;

        DEPTNO DNAME          LOC              AVG_SAL    MAX_SAL
    ---------- -------------- ------------- ---------- ----------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON

    SQL> UPDATE  DEPT_STAT
      2  SET     AVG_SAL = 0,
      3          MAX_SAL = 0
      4  WHERE   DEPTNO  = 10;

    1 row updated.

    SQL> select * from DEPT_STAT;

        DEPTNO DNAME          LOC              AVG_SAL    MAX_SAL
    ---------- -------------- ------------- ---------- ----------
            10 ACCOUNTING     NEW YORK               0          0
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON

    SQL> rollback;

    Rollback complete.

    SQL>
     
    Update based on Single Value

    Here it tries to update the Table with single value retrieved from sub query.

    Code (Text):

    SQL> UPDATE  DEPT_STAT
      2  SET     MAX_SAL = (SELECT MAX(SAL)
      3                     FROM   EMP
      4                     WHERE  DEPTNO=10
      5                    );

    4 rows updated.

    SQL> select * from DEPT_STAT;

        DEPTNO DNAME          LOC              AVG_SAL    MAX_SAL
    ---------- -------------- ------------- ---------- ----------
            10 ACCOUNTING     NEW YORK                       5000
            20 RESEARCH       DALLAS                         5000
            30 SALES          CHICAGO                        5000
            40 OPERATIONS     BOSTON                         5000

    SQL> rollback;

    Rollback complete.

    SQL>
     
    Though it is updated , it is not what we need. It updated 4 rows insetead on One row (deprno=10) . So we need to restrict the updation with WHERE clause.

    Code (Text):

    SQL> UPDATE  DEPT_STAT
      2  SET     MAX_SAL = (SELECT MAX(SAL)
      3                     FROM   EMP
      4                     WHERE  DEPTNO=10
      5                    )
      6  WHERE DEPTNO=10;

    1 row updated.

    SQL> select * from DEPT_STAT;

        DEPTNO DNAME          LOC              AVG_SAL    MAX_SAL
    ---------- -------------- ------------- ---------- ----------
            10 ACCOUNTING     NEW YORK                       5000
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON

    SQL> rollback;

    Rollback complete.

    SQL>
     
    Update based on Multiple Values

    Here it tried to update the table with the multiple values from a normal sub query . it could be done in two ways as below.

    Code (Text):

    SQL> UPDATE  DEPT_STAT
      2  SET     MAX_SAL = (SELECT MAX(SAL)
      3                     FROM   EMP
      4                     WHERE  DEPTNO=10
      5                    ),
      6          AVG_SAL = (SELECT AVG(SAL)
      7                     FROM   EMP
      8                     WHERE  DEPTNO=10
      9                    )
     10  WHERE DEPTNO=10;

    1 row updated.

    SQL> select * from DEPT_STAT;

        DEPTNO DNAME          LOC              AVG_SAL    MAX_SAL
    ---------- -------------- ------------- ---------- ----------
            10 ACCOUNTING     NEW YORK            2917       5000
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON

    SQL> rollback;

    Rollback complete.

    SQL> select * from DEPT_STAT;

        DEPTNO DNAME          LOC              AVG_SAL    MAX_SAL
    ---------- -------------- ------------- ---------- ----------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON

    SQL> UPDATE  DEPT_STAT
      2  SET     (MAX_SAL,AVG_SAL ) = (SELECT MAX(SAL) ,AVG(SAL)
      3                                FROM   EMP
      4                                WHERE  DEPTNO=10
      5                    )
      6  WHERE DEPTNO=10;

    1 row updated.

    SQL> select * from DEPT_STAT;

        DEPTNO DNAME          LOC              AVG_SAL    MAX_SAL
    ---------- -------------- ------------- ---------- ----------
            10 ACCOUNTING     NEW YORK            2917       5000
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON

    SQL> rollback;

    Rollback complete.

    SQL>
     
    You can see that the second one is more effective and elegant method.

    Update based on Single Value ( Correlated subquery )

    Here it tries to update the table with the Correlated subquery with single value turned from the subquery. Correlated subquery has two parts. One main query and inner subquery and for each main query subquery is executed once or more than once .

    Here correlated subquery tries to update the table for each department's maximum salary.

    Code (Text):

    SQL> select * from DEPT_STAT;

        DEPTNO DNAME          LOC              AVG_SAL    MAX_SAL
    ---------- -------------- ------------- ---------- ----------
            10 ACCOUNTING     NEW YORK               0          0
            20 RESEARCH       DALLAS                 0          0
            30 SALES          CHICAGO                0          0
            40 OPERATIONS     BOSTON                 0          0

    SQL> UPDATE  DEPT_STAT D
      2  SET     (MAX_SAL ) = (SELECT MAX(SAL)
      3                                FROM   EMP E
      4                                WHERE  E.DEPTNO=D.DEPTNO
      5                       ) ;

    4 rows updated.

    SQL> select * from DEPT_STAT;

        DEPTNO DNAME          LOC              AVG_SAL    MAX_SAL
    ---------- -------------- ------------- ---------- ----------
            10 ACCOUNTING     NEW YORK               0       5000
            20 RESEARCH       DALLAS                 0       3000
            30 SALES          CHICAGO                0       2850
            40 OPERATIONS     BOSTON                 0

    SQL> rollback;

    Rollback complete.

    SQL>
     
    Though it is updated , it is updated wrongly for the Deptno=40 as NULL. (There is no employees for DEPTNO=40). So it should be unaffetcted. This can be achieved in two ways as below.

    Code (Text):

    SQL> select * from DEPT_STAT;                                                                                                                 DEPTNO DNAME          LOC              AVG_SAL    MAX_SAL        ---------- -------------- ------------- ---------- ----------                10 ACCOUNTING     NEW YORK               0          0                20 RESEARCH       DALLAS                 0          0                30 SALES          CHICAGO                0          0                40 OPERATIONS     BOSTON                 0          0                                                                            
    SQL> UPDATE  DEPT_STAT D                                               2  SET     (MAX_SAL) = (SELECT MAX(SAL)                              3                                FROM   EMP E                        4                                WHERE  E.DEPTNO=D.DEPTNO            5                    )                                               6  WHERE DEPTNO IN ( SELECT DEPTNO                                   7                    FROM   EMP );                                                                                                      3 rows updated.                                                                                                                           SQL> select * from DEPT_STAT;                                                                                                                 DEPTNO DNAME          LOC              AVG_SAL    MAX_SAL        ---------- -------------- ------------- ---------- ----------                10 ACCOUNTING     NEW YORK               0       5000                20 RESEARCH       DALLAS                 0       3000                30 SALES          CHICAGO                0       2850                40 OPERATIONS     BOSTON                 0          0                                                                             SQL> rollback;                                                                                                                            Rollback complete.                                                                                                                        SQL> select * from DEPT_STAT;                                                                                                                 DEPTNO DNAME          LOC              AVG_SAL    MAX_SAL        ---------- -------------- ------------- ---------- ----------                10 ACCOUNTING     NEW YORK               0          0                20 RESEARCH       DALLAS                 0          0                30 SALES          CHICAGO                0          0                40 OPERATIONS     BOSTON                 0          0                                                                             SQL> UPDATE  DEPT_STAT D
      2  SET     (MAX_SAL ) = (SELECT MAX(SAL)
      3                                FROM   EMP E
      4                                WHERE  E.DEPTNO=D.DEPTNO
      5                    )
      6  WHERE EXISTS ( SELECT NULL
      7                 FROM   EMP E
      8                 WHERE  E.DEPTNO=D.DEPTNO);

    3 rows updated.

    SQL> select * from DEPT_STAT;

        DEPTNO DNAME          LOC              AVG_SAL    MAX_SAL
    ---------- -------------- ------------- ---------- ----------
            10 ACCOUNTING     NEW YORK               0       5000
            20 RESEARCH       DALLAS                 0       3000
            30 SALES          CHICAGO                0       2850
            40 OPERATIONS     BOSTON                 0          0

    SQL> rollback;

    Rollback complete.

    SQL>
     
    WHERE EXISTS will be better in terms of perfomance.

    Update based on Multiple Values ( Correlated subquery )

    Here it tries t update the table with the Correlated subquery with multiple value turned from value subquery. ie, it updates both MAX_SAL and AVG_SAL using the single SQL.

    Here also you can have two methods for the same .

    Code (Text):

    SQL> select * from DEPT_STAT;

        DEPTNO DNAME          LOC              AVG_SAL    MAX_SAL
    ---------- -------------- ------------- ---------- ----------
            10 ACCOUNTING     NEW YORK               0          0
            20 RESEARCH       DALLAS                 0          0
            30 SALES          CHICAGO                0          0
            40 OPERATIONS     BOSTON                 0          0

    SQL> UPDATE  DEPT_STAT D
      2  SET     MAX_SAL  = (SELECT MAX(SAL)
      3                      FROM   EMP E
      4                      WHERE  E.DEPTNO=D.DEPTNO
      5                      ) ,
      6          AVG_SAL  = (SELECT AVG(SAL)
      7                      FROM   EMP E
      8                      WHERE  E.DEPTNO=D.DEPTNO
      9                     )
     10  WHERE EXISTS ( SELECT NULL
     11                 FROM   EMP E
     12                 WHERE  E.DEPTNO=D.DEPTNO)
     13  ;

    3 rows updated.

    SQL> select * from DEPT_STAT;

        DEPTNO DNAME          LOC              AVG_SAL    MAX_SAL
    ---------- -------------- ------------- ---------- ----------
            10 ACCOUNTING     NEW YORK            2917       5000
            20 RESEARCH       DALLAS              2175       3000
            30 SALES          CHICAGO             1567       2850
            40 OPERATIONS     BOSTON                 0          0

    SQL> rollback;

    Rollback complete.

    SQL> UPDATE  DEPT_STAT D
      2  SET     (MAX_SAL,AVG_SAL ) = (SELECT MAX(SAL) ,AVG(SAL)
      3                                FROM   EMP E
      4                                WHERE  E.DEPTNO=D.DEPTNO
      5                    )
      6  WHERE EXISTS ( SELECT NULL
      7                 FROM   EMP E
      8                 WHERE  E.DEPTNO=D.DEPTNO);

    3 rows updated.

    SQL> select * from DEPT_STAT;

        DEPTNO DNAME          LOC              AVG_SAL    MAX_SAL
    ---------- -------------- ------------- ---------- ----------
            10 ACCOUNTING     NEW YORK            2917       5000
            20 RESEARCH       DALLAS              2175       3000
            30 SALES          CHICAGO             1567       2850
            40 OPERATIONS     BOSTON                 0          0

    SQL> commit;

    Commit complete.

    SQL>
     
    Here we can see that Second method is straight and beautiful .

    [PAGE]INSERT using records[/PAGE]

    All of the above SQL syntax will apply to the PL/SQL also. Moreover there are some special syntaxes exclusively applicable for the PL/SQL.

    Insert using Record

    In PL/SQL table can be updated using the Records where the column values are sotored instead of updating each column.

    Code (Text):

    SQL> TRUNCATE TABLE EMP_MGR;

    Table truncated.

    SQL> insert into EMP_MGR(EMPNO) SELECT EMPNO FROM EMP;

    14 rows created.

    SQL> commit;

    Commit complete.

    SQL> SELECT EMPNO,DEPTNO,JOB,SAL FROM EMP_MGR;

         EMPNO     DEPTNO JOB              SAL
    ---------- ---------- --------- ----------
          7369
          7499
          7521
          7566
          7654
          7698
          7782
          7788
          7839
          7844
          7876
          7900
          7902
          7934

    14 rows selected.

    SQL> DECLARE
      2       EMPLOYEE  EMP%ROWTYPE;
      3      BEGIN
      4        FOR R IN ( SELECT * FROM EMP )
      5        LOOP
      6
      7        EMPLOYEE := R ;
      8
      9        UPDATE EMP_MGR
     10           SET ROW = EMPLOYEE
     11         WHERE EMPNO=EMPLOYEE.EMPNO;
     12
     13       END LOOP;
     14
     15       COMMIT;
     16  END;
     17  /

    PL/SQL procedure successfully completed.

    SQL> SELECT EMPNO,DEPTNO,JOB,SAL FROM EMP_MGR;

         EMPNO     DEPTNO JOB              SAL
    ---------- ---------- --------- ----------
          7369         20 CLERK            800
          7499         30 SALESMAN        1600
          7521         30 SALESMAN        1250
          7566         20 MANAGER         2975
          7654         30 SALESMAN        1250
          7698         30 MANAGER         2850
          7782         10 MANAGER         2450
          7788         20 ANALYST         3000
          7839         10 PRESIDENT       5000
          7844         30 SALESMAN        1500
          7876         20 CLERK           1100
          7900         30 CLERK            950
          7902         20 ANALYST         3000
          7934         10 CLERK           1300

    14 rows selected.

    SQL>
     
    UPDATE with RETURNING clause

    RETURNING Clause along with the UPDATE returns the updated value to a variable or collection . And this can be processed for further use. This avoids the another select statement after update and fetches the record with the same update statement.

    Code (Text):


    SQL> SELECT EMPNO,DEPTNO,JOB,SAL FROM EMP_MGR;

         EMPNO     DEPTNO JOB              SAL
    ---------- ---------- --------- ----------
          7369         20 CLERK            800
          7499         30 SALESMAN        1600
          7521         30 SALESMAN        1250
          7566         20 MANAGER         2975
          7654         30 SALESMAN        1250
          7698         30 MANAGER         2850
          7782         10 MANAGER         2450
          7788         20 ANALYST         3000
          7839         10 PRESIDENT       5000
          7844         30 SALESMAN        1500
          7876         20 CLERK           1100
          7900         30 CLERK            950
          7902         20 ANALYST         3000
          7934         10 CLERK           1300

    14 rows selected.

    SQL> DECLARE
      2   EMPLOYEE  EMP_MGR%ROWTYPE;
      3   ENAME_V   EMP_MGR.ENAME%TYPE;
      4   JOB_V     EMP_MGR.JOB%TYPE;
      5   SAL_N     EMP_MGR.SAL%TYPE;
      6  BEGIN
      7      FOR R IN ( SELECT * FROM EMP_MGR )
      8      LOOP
      9
     10      EMPLOYEE := R ;
     11
     12      UPDATE EMP_MGR
     13         SET SAL= DECODE(JOB,'MANAGER',SAL*1.2,
     14                        'ANALYST',SAL*1.4,
     15                              'CLERK',SAL*.8,
     16                              SAL)
     17      WHERE EMPNO=R.EMPNO
     18      returning ENAME,JOB,SAL
     19      into ENAME_V,JOB_V,SAL_N;
     20
     21      DBMS_OUTPUT.PUT_LINE (LPAD(ENAME_V,15,'_')||';'||LPAD(JOB_V,15,'_')||';'||SAL_N);
     22
     23      END LOOP;
     24
     25      COMMIT;
     26    END;
     27  /
    __________SMITH;__________CLERK;640
    __________ALLEN;_______SALESMAN;1600
    ___________WARD;_______SALESMAN;1250
    __________JONES;________MANAGER;3570
    _________MARTIN;_______SALESMAN;1250
    __________BLAKE;________MANAGER;3420
    __________CLARK;________MANAGER;2940
    __________SCOTT;________ANALYST;4200
    ___________KING;______PRESIDENT;5000
    _________TURNER;_______SALESMAN;1500
    __________ADAMS;__________CLERK;880
    __________JAMES;__________CLERK;760
    ___________FORD;________ANALYST;4200
    _________MILLER;__________CLERK;1040

    PL/SQL procedure successfully completed.

    SQL>

     
    [PAGE]UPDATE with WHERE CURRENT OF[/PAGE]

    There is one more important UPDATE syntax in pl/sql with 'WHERE CURRENT OF' clause. It is basically used in UPDATE and DELETE statements and it avoid the need of normal WHERE cause for the update statement.

    The WHERE CURRENT OF clause in such statement states that the most recent row fetched from the table should be updated or deleted.For that we must declare the cursor with the FOR UPDATE clause to use this feature.
    When the session opens a cursor with the FOR UPDATE clause, all rows in the return set will hold row-level exclusive locks. Other sessions can only query the rows, but they cannot update, delete, or select with FOR UPDATE.

    Here is an example for FOR UPDATE .. WHERE CURRENT OF with UPDATE statement.

    Code (Text):

    SQL> SELECT EMPNO,DEPTNO,JOB,SAL,COMM FROM EMP_MGR;

         EMPNO     DEPTNO JOB              SAL       COMM
    ---------- ---------- --------- ---------- ----------
          7369         20 CLERK            800          0
          7499         30 SALESMAN        1600          0
          7521         30 SALESMAN        1250          0
          7566         20 MANAGER         2975          0
          7654         30 SALESMAN        1250          0
          7698         30 MANAGER         2850          0
          7782         10 MANAGER         2450          0
          7788         20 ANALYST         3000          0
          7839         10 PRESIDENT       5000          0
          7844         30 SALESMAN        1500          0
          7876         20 CLERK           1100          0
          7900         30 CLERK            950          0
          7902         20 ANALYST         3000          0
          7934         10 CLERK           1300          0

    14 rows selected.

    SQL>
    SQL> DECLARE
      2         EMPLOYEE  EMP%ROWTYPE;
      3
      4         CURSOR c1 IS
      5         SELECT * FROM EMP_MGR
      6         FOR UPDATE OF COMM;
      7
      8  BEGIN
      9          FOR R IN C1
     10          LOOP
     11
     12          EMPLOYEE := R ;
     13
     14          UPDATE EMP_MGR
     15             SET COMM= DECODE(JOB,'MANAGER',SAL*0.10,
     16                             'ANALYST',SAL*0.05,
     17                                            0)
     18           WHERE CURRENT OF C1 ;
     19
     20     END LOOP;
     21
     22         COMMIT;
     23  END;
     24  /

    PL/SQL procedure successfully completed.

    SQL> SELECT EMPNO,DEPTNO,JOB,SAL,COMM FROM EMP_MGR;

         EMPNO     DEPTNO JOB              SAL       COMM
    ---------- ---------- --------- ---------- ----------
          7369         20 CLERK            800          0
          7499         30 SALESMAN        1600          0
          7521         30 SALESMAN        1250          0
          7566         20 MANAGER         2975      297.5
          7654         30 SALESMAN        1250          0
          7698         30 MANAGER         2850        285
          7782         10 MANAGER         2450        245
          7788         20 ANALYST         3000        150
          7839         10 PRESIDENT       5000          0
          7844         30 SALESMAN        1500          0
          7876         20 CLERK           1100          0
          7900         30 CLERK            950          0
          7902         20 ANALYST         3000        150
          7934         10 CLERK           1300          0

    14 rows selected.

    SQL>