delete multiple tables with single query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: delete multiple tables with single query

Hybrid View

  1. #1
    Join Date
    Dec 2000
    Posts
    95

    Question

    Hi

    Can we delete rows from multiple tables with a single query (like we are retrieving rows from multiple tables using select statement) ?

    for example:

    these are my tables

    SQL> select * from test12;

    ID NAME
    --------- ----------
    100 srini
    200 venu
    300 vega

    SQL> select * from test13;

    ID ADDRESS
    --------- ----------
    300 add1
    200 add2
    100 add3


    and i have tried to delete as follows

    SQL> delete test12,test13 where test12.id=300 and test12.id=test13.id;
    delete test12,test13 where test12.id=300 and test12.id=test13.id
    *
    ERROR at line 1:
    ORA-00933: SQL command not properly ended


    this is the problem


    thanks for any help

    SrinivasM

  2. #2
    Join Date
    Feb 2001
    Posts
    123
    There are a couple of problems with your delete statements:

    1) You can only delete from one table with a single delete statement

    2) The keyword FROM was missing - eg.

    delete from test12 where ...

    If you need to delete from both tables, then is it possible to use a foreign key constraint with on delete cascade?

    Failing that, I guess you are going to have to write a PL/SQL function to do the job.

    HTH

    David.

  3. #3
    Join Date
    Dec 2000
    Posts
    95

    Question delet rows from multiple tables with a single query

    Hi David,

    Can you give a sample query how to use the cascade with delete statement.

    I have tried like this , but it is failing

    ---------------------------------------------------------------------
    SQL> create table test14 (id number primary key,name varchar2(10));

    Table created.

    SQL> create table test15 (id number,address varchar2(10), foreign key (id) references test14(id));

    Table created.

    -----------------------------------------------------------------------

    these are the rows in the tables

    --------------------------------------------------------------------

    SQL> select * from test14;

    ID NAME
    --------- ----------
    100 srini
    200 vijay
    300 ramu

    SQL> select * from test15;

    ID ADDRESS
    --------- ----------
    100 add1
    200 add2
    300 add3
    ------------------------------------------------------------------------

    I have tried to delete the rows with cascade



    SQL> delete from test14 where id=300 cascade;
    delete from test14 where id=300 cascade
    *
    ERROR at line 1:
    ORA-00933: SQL command not properly ended


    Thankyou


    SrinivasM

  4. #4
    Join Date
    Feb 2001
    Posts
    123
    Hi,

    The on delete cascade option is part of the constraint definition, not the delete command - here is a paste from the Oracle documentation (V7 unfortunately - the only one I have handy at the mo :( ), but it should apply equally to Oracle 8...
    -----------------
    Maintaining Referential Integrity with the ON DELETE CASCADE Option
    If you use the ON DELETE CASCADE option, Oracle7 permits deletions of referenced key values in the parent table and automatically deletes dependent rows in the child table to maintain referential integrity.
    Example VII

    This example creates the EMP table, defines and enables the referential integrity constraint FK_DEPTNO, and uses the ON DELETE CASCADE option:


    CREATE TABLE emp
    (empno NUMBER(4),
    ename VARCHAR2(10),
    job VARCHAR2(9),
    mgr NUMBER(4),
    hiredate DATE,
    sal NUMBER(7,2),
    comm NUMBER(7,2),
    deptno NUMBER(2) CONSTRAINT fk_deptno
    REFERENCES dept(deptno)
    ON DELETE CASCADE )
    Because of the ON DELETE CASCADE option, Oracle7 cascades any deletion of a DEPTNO value in the DEPT table to the DEPTNO values of its dependent rows of the EMP table. For example, if department 20 is deleted from the DEPT table, Oracle7 deletes the department's employees from the EMP table.

    -------------

    HTH

    David.

  5. #5
    Join Date
    Dec 2000
    Posts
    95

    Smile deleting rows from multiple tables with a single query

    HI David ,

    I got it. It is like below

    --------------------------------------------------------------------
    SQL> create table test16 (id number primary key,name varchar2(10));

    Table created.


    SQL> create table test17 (id number,address varchar2(10), foreign key (id) references test16(id) on
    delete cascade);

    Table created.


    ------------------------------------------------------------------------
    these are the rows of the tables

    SQL> select * from test16;

    ID NAME
    --------- ----------
    100 srini
    200 ram
    300 sam

    SQL> select * from test17;

    ID ADDRESS
    --------- ----------
    100 add1
    200 add2
    300 add3

    -----------------------------------------------------------------------

    deleting the row in one table , so that it will delete the foreign keys of the child table.

    SQL> delete from test16 where id=300;

    1 row deleted.

    ---------------------------------------------------------------------
    And checking whether the rows are deleted or not

    SQL> select * from test16;

    ID NAME
    --------- ----------
    100 srini
    200 ram

    SQL> select * from test17;

    ID ADDRESS
    --------- ----------
    100 add1
    200 add2



    Thank you David

    SrinivasM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width