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!

DELETE statements in SQL

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

  1. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    DELETE is another widely used DML in SQL . It is used to delete the records with some values specified in the query. 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_MAST;

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

    SQL>
     
    Delete All Records

    Here it deletes all the records for the table mentioned in the statement.

    Code (Text):

    SQL> DELETE FROM DEPT_MAST;

    4 rows deleted.

    SQL> SELECT * FROM DEPT_MAST

    no rows selected

    SQL> rollback;

    Rollback complete.

    SQL>
     
    Delete Specified Record

    Here it deletes the records for the resricted values given in the query. Restriction is made possible by using WHERE Clause.

    Code (Text):

    SQL> SELECT * FROM DEPT_MAST ;

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

    SQL> DELETE FROM DEPT_MAST WHERE  DEPTNO=40;

    1 row deleted.

    SQL> SELECT * FROM DEPT_MAST ;

        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO

    SQL> rollback;

    Rollback complete.

    SQL>
     
    DELETE based on subquery

    Here It deletes the records based on the result returned by normal subquery. Subquery may give single or multiple values. Notice how the delete query needs to change accordingly.

    Code (Text):

    SQL> SELECT * FROM DEPT_MAST ;

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

    SQL> DELETE FROM DEPT_MAST
      2  WHERE  DEPTNO =(SELECT MIN(DEPTNO) FROM EMP_MGR);

    1 row deleted.

    SQL> SELECT * FROM DEPT_MAST ;

        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON

    SQL> ROLLBACK;

    Rollback complete.

    SQL> DELETE FROM DEPT_MAST
      2  WHERE  DEPTNO =(SELECT DEPTNO FROM EMP_MGR);
    WHERE  DEPTNO =(SELECT DEPTNO FROM EMP_MGR)
                    *
    ERROR at line 2:
    ORA-01427: single-row subquery returns more than one row


    SQL> DELETE FROM DEPT_MAST
      2  WHERE  DEPTNO IN (SELECT DEPTNO FROM EMP_MGR);

    3 rows deleted.

    SQL> SELECT * FROM DEPT_MAST ;

        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            40 OPERATIONS     BOSTON

    SQL> ROLLBACK;

    Rollback complete.

    SQL>
     
    DELETE using Correlated subquery

    Here it tries to delete the records using correlated 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 . First query tries with EXISTS clause and second query tries with NOT EXISTS clause.



    Code (Text):

    SQL> SELECT * FROM DEPT_MAST ;

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

    SQL> DELETE FROM DEPT_MAST D
      2  WHERE  EXISTS(SELECT NULL
      3                FROM   EMP_MGR E
      4                WHERE  D.DEPTNO = E.DEPTNO);

    3 rows deleted.

    SQL> SELECT * FROM DEPT_MAST ;

        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            40 OPERATIONS     BOSTON

    SQL> ROLLBACK;

    Rollback complete.

    SQL> DELETE FROM DEPT_MAST D
      2  WHERE  NOT EXISTS(SELECT NULL
      3                    FROM   EMP_MGR E
      4                    WHERE  D.DEPTNO = E.DEPTNO);

    1 row deleted.

    SQL> SELECT * FROM DEPT_MAST ;

        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO

    SQL> ROLLBACK;

    Rollback complete.

    SQL> SELECT * FROM DEPT_MAST ;

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

    SQL>
     
    [PAGE]DELETE with RETURN Clause[/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.

    DELETE with return clause

    Code (Text):

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

         EMPNO ENAME          DEPTNO JOB              SAL
    ---------- ---------- ---------- --------- ----------
          7369 SMITH              20 CLERK            640
          7499 ALLEN              30 SALESMAN        1600
          7521 WARD               30 SALESMAN        1250
          7566 JONES              20 MANAGER         3570
          7654 MARTIN             30 SALESMAN        1250
          7698 BLAKE              30 MANAGER         3420
          7782 CLARK              10 MANAGER         2940
          7788 SCOTT              20 ANALYST         4200
          7839 KING               10 PRESIDENT       5000
          7844 TURNER             30 SALESMAN        1500
          7876 ADAMS              20 CLERK            880
          7900 JAMES              30 CLERK            760
          7902 FORD               20 ANALYST         4200
          7934 MILLER             10 CLERK           1040

    14 rows selected.

    SQL> SET SERVEROUT ON
    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 WHERE DEPTNO=10)
      8         LOOP
      9
     10         DELETE EMP_MGR
     11         WHERE EMPNO=R.EMPNO
     12         returning ENAME,JOB,SAL
     13         into ENAME_V,JOB_V,SAL_N;
     14
     15         DBMS_OUTPUT.PUT_LINE (LPAD(ENAME_V,15,'_')||';'||LPAD(JOB_V,15,'_')|
    |';'||SAL_N);
     16
     17         END LOOP;
     18
     19         COMMIT;
     20
     21  END;
     22  /
    __________CLARK;________MANAGER;2940
    ___________KING;______PRESIDENT;5000
    _________MILLER;__________CLERK;1040

    PL/SQL procedure successfully completed.

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

         EMPNO ENAME          DEPTNO JOB              SAL
    ---------- ---------- ---------- --------- ----------
          7369 SMITH              20 CLERK            640
          7499 ALLEN              30 SALESMAN        1600
          7521 WARD               30 SALESMAN        1250
          7566 JONES              20 MANAGER         3570
          7654 MARTIN             30 SALESMAN        1250
          7698 BLAKE              30 MANAGER         3420
          7788 SCOTT              20 ANALYST         4200
          7844 TURNER             30 SALESMAN        1500
          7876 ADAMS              20 CLERK            880
          7900 JAMES              30 CLERK            760
          7902 FORD               20 ANALYST         4200

    11 rows selected.

    SQL>
     
    [PAGE]DELETE with WHERE CURRENT OF[/PAGE]
    Here is one more important DELETE 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 such 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 DELETE 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
      7         CURSOR c1 IS
      8         SELECT *
      9         FROM EMP_MGR
     10         WHERE DEPTNO=10
     11         FOR UPDATE OF COMM;
     12
     13  BEGIN
     14           FOR R IN C1
     15           LOOP
     16
     17           DELETE EMP_MGR
     18           WHERE CURRENT OF C1;
     19
     20
     21           END LOOP;
     22  commit;
     23  END;
     24  /

    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
          7788         20 ANALYST         3000
          7844         30 SALESMAN        1500
          7876         20 CLERK           1100
          7900         30 CLERK            950
          7902         20 ANALYST         3000

    11 rows selected.

    SQL>
     
     
  2. ShaheerBadar

    ShaheerBadar Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    thanks you
     
  3. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    hi raj, nice one. returning clause will return the values which is deleting, into plsql variables right
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, it does.
     
  5. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    hi raj, i have one doubt on mutating table error. what i mean that is

    ex:
    create or replace trigger mutate_demo
    after insert
    on emp
    for each row
    begin
    insert into emp values(....);
    end;
    i am not concentrating on the code and syntax errors.

    here in the code, i am writing trigger on emp and trying to insert values into emp table.
    here mutating table is emp and the error is mutating table error, occurs because i am trying to insert the values into same emp table. mutating table error wont occur if i perform dml operations on except emp table.

    is that right what i mean?