OraFAQ Forum: SQL & PL/SQL » delete rows from multiple tables (more than 2 tables)

Home » SQL & PL/SQL » SQL & PL/SQL » delete rows from multiple tables (more than 2 tables)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

delete rows from multiple tables (more than 2 tables) [message #148134]

Tue, 22 November 2005 21:35

ndefontenay
Messages: 14
Registered: November 2005
Location: Thailand

Junior Member

Hi everyone,

I have been browsing the whole day for that one and it seems that despite the volume of question asked for the same topic no answer has been given.

Question: Is it possible to delete rows from multiple tables?

I use Oracle 8.1.7.
My table are the following:

SUPPLIER (main supplier table ID is private key and indexed)
SUPDEALER (foreign key is SUP_ID from table SUPPLIER)
SUPHOUR (foreign key is SUP_ID from table SUPPLIER)
SUPCONTACT (foreign key is SUP_ID from table SUPPLIER)

I have to delete some records based on a supplier ID in all of them.

I know, I can delete in the 3 tables using the same WHERE clauses but there must be a shorter way!

So, the query I wrote looks like mysql syntax:

DELETE 
FROM SUPPLIER, SUPDEALER, SUPHOUR, SUPCONTACT
WHERE SUPPLIER.ID = SUPDEALER.SUP_ID
AND SUPPLIER.ID = SUPHOUR.SUP_ID
AND SUPPLIER.ID = SUPCONTACT.SUP_ID
AND SUPPLIER.ID = 12564

With that piece of SQL I got the following error message:

Ora-00933: SQL command not properly ended.
It shows my FROM Clause having a problem.

For those interested, the documentation of mysql is very clear on the topic.

http://dev.mysql.com/doc/refman/5.0/en/delete.html

I've take a look to this link too:
http://sqlzoo.napier.ac.uk/big/B/s/a/statements_86a.htm#2065924
but the samples are again very simple.

So the question is: Is it possible to get an answer as clear as that mysql link or is it impossible to do it with Oracle?

Thanks so much to anybody answering to this question. A link to a clear syntax description from Oracle would do as well as a working sample for Oracle.

Report message to a moderator

Re: delete rows from multiple tables (more than 2 tables) [message #148167 is a reply to message #148134]

Wed, 23 November 2005 00:04

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

You can create a trigger that will automtically delete from the child tables when the corresponding id is deleted from the parent table, as demonstrated below.

-- tables, primary and foreign keys, and data:

scott@ORA92> CREATE TABLE SUPPLIER
  2    (id NUMBER PRIMARY KEY)
  3  /

Table created.

scott@ORA92> INSERT INTO supplier VALUES (12564)
  2  /

1 row created.

scott@ORA92> CREATE TABLE SUPDEALER
  2    (sup_id NUMBER REFERENCES supplier (id))
  3  /

Table created.

scott@ORA92> INSERT INTO supdealer VALUES (12564)
  2  /

1 row created.

scott@ORA92> CREATE TABLE SUPHOUR
  2    (sup_id NUMBER REFERENCES supplier (id))
  3  /

Table created.

scott@ORA92> INSERT INTO suphour VALUES (12564)
  2  /

1 row created.

scott@ORA92> CREATE TABLE SUPCONTACT
  2    (sup_id NUMBER REFERENCES supplier (id))
  3  /

Table created.

scott@ORA92> INSERT INTO supcontact VALUES (12564)
  2  /

1 row created.

scott@ORA92> COMMIT
  2  /

Commit complete.

-- trigger:

scott@ORA92> CREATE OR REPLACE TRIGGER supplier_bir
  2    BEFORE DELETE ON supplier
  3    FOR EACH ROW
  4  BEGIN
  5    DELETE FROM supdealer WHERE sup_id = :OLD.id;
  6    DELETE FROM suphour WHERE sup_id = :OLD.id;
  7    DELETE FROM supcontact WHERE sup_id = :OLD.id;
  8  END supplier_bir;
  9  /

Trigger created.

scott@ORA92> SHOW ERRORS
No errors.

-- delete:

scott@ORA92> DELETE FROM SUPPLIER
  2  WHERE SUPPLIER.ID = 12564
  3  /

1 row deleted.

-- results:

scott@ORA92> SELECT * FROM supplier
  2  /

no rows selected

scott@ORA92> SELECT * FROM supdealer
  2  /

no rows selected

scott@ORA92> SELECT * FROM suphour
  2  /

no rows selected

scott@ORA92> SELECT * FROM supcontact
  2  /

no rows selected

scott@ORA92>

Report message to a moderator

Re: delete rows from multiple tables (more than 2 tables) [message #148168 is a reply to message #148167]

Wed, 23 November 2005 00:10

lijok
Messages: 68
Registered: April 2005

Member

OR if you need a cascade delete, which means when the parent record is deleted, delete all the child records in other tables referencing to this record.

Add a ON DELETE CASCADE constraint to your foreign keys

Rgds,
Lijo

Report message to a moderator

Re: delete rows from multiple tables (more than 2 tables) [message #148174 is a reply to message #148168]

Wed, 23 November 2005 00:24

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

True, as shown below.

-- tables, primary and foreign keys, and data:

scott@ORA92> CREATE TABLE SUPPLIER
  2    (id NUMBER PRIMARY KEY)
  3  /

Table created.

scott@ORA92> INSERT INTO supplier VALUES (12564)
  2  /

1 row created.

scott@ORA92> CREATE TABLE SUPDEALER
  2    (sup_id NUMBER REFERENCES supplier (id)
  3  	ON DELETE CASCADE)
  4  /

Table created.

scott@ORA92> INSERT INTO supdealer VALUES (12564)
  2  /

1 row created.

scott@ORA92> CREATE TABLE SUPHOUR
  2    (sup_id NUMBER REFERENCES supplier (id)
  3  	ON DELETE CASCADE)
  4  /

Table created.

scott@ORA92> INSERT INTO suphour VALUES (12564)
  2  /

1 row created.

scott@ORA92> CREATE TABLE SUPCONTACT
  2    (sup_id NUMBER REFERENCES supplier (id)
  3  	ON DELETE CASCADE)
  4  /

Table created.

scott@ORA92> INSERT INTO supcontact VALUES (12564)
  2  /

1 row created.

scott@ORA92> COMMIT
  2  /

Commit complete.

-- delete:

scott@ORA92> DELETE FROM SUPPLIER
  2  WHERE SUPPLIER.ID = 12564
  3  /

1 row deleted.

-- results:

scott@ORA92> SELECT * FROM supplier
  2  /

no rows selected

scott@ORA92> SELECT * FROM supdealer
  2  /

no rows selected

scott@ORA92> SELECT * FROM suphour
  2  /

no rows selected

scott@ORA92> SELECT * FROM supcontact
  2  /

no rows selected

scott@ORA92>

Report message to a moderator

Re: delete rows from multiple tables (more than 2 tables) [message #148186 is a reply to message #148134]

Wed, 23 November 2005 00:47

ndefontenay
Messages: 14
Registered: November 2005
Location: Thailand

Junior Member

Hi.

I've read these answers before in other threads. I agree that technically it works but...

The problem with my database is that there is a big application running on top of it using forms. It has been developed a couple of years before I join the company and there is no detailed technical documentation on it.

To perform my delete command, I don't want to use any trigger or constraints that could affect the application.

I would really like to see a single SQL command that can do it. There must be a way.

I've read the documentation on the SQL reference I've linked in my thread:

http://sqlzoo.napier.ac.uk/big/B/s/a/statements_86a.htm#2117791

And I can see in the FROM clause "dml_table_expression_clause". Down, there's more detail for this:

and I see there is a "table_collection_expression".

Haha!!! There must be some light around this expression.

So I've checked the explanation of this table_collection expression in the same document (further down) and I got this:

You can use a table_collection_expression in a correlated subquery to delete rows with values that also exist in another table.
See Also:

"Table Collections: Examples"-->http://sqlzoo.napier.ac.uk/big/B/s/a/statements_103a.htm#2071644

So far, I've understand that Oracle can delete rows in mulTIple tables only if he sees it as a single table.

I think that something like:

DELETE FROM
(SELECT * FROM SUPPLIER, SUPDEALER, SUPHOUR, SUPCONTACT
WHERE SUPPLIER.ID = SUPDEALER.SUP_ID
AND SUPPLIER.ID = SUPHOUR.SUP_ID
AND SUPPLIER.ID = SUPCONTACT
AND SUPPLIER.ID = 25468)

My faith in Oracle is coming back.

If this work I think I will post a new thread with a clear explanation because I've seen it nowhere yet...

Report message to a moderator

Re: delete rows from multiple tables (more than 2 tables) [message #148196 is a reply to message #148134]

Wed, 23 November 2005 01:05

ndefontenay
Messages: 14
Registered: November 2005
Location: Thailand

Junior Member

Update:

I've try to make a nested query to delete my rows on many tables and got the following message:

ORA-01752: cannot delete from view without exactly one key-preserved table

Cause: The deleted table either had no key preserved tables, had more than one key-preserved table, or the key-preserved table was an unmerged view or a table from a read-only view.

Action: Redefine the view or delete it from the underlying base tables.

Then I've tried to select distinct ID from each table to work around this and got:

ORA-01732: data manipulation operation not legal on this view

Cause: An attempt was made to use an UPDATE, INSERT, or DELETE statement on a view that contains expressions or functions or was derived from more than one table. If a join operation was used to create the view or the view contains virtual columns derived from functions or expressions, then the view may only be queried.

Action: UPDATE, INSERT, or DELETE rows in the base tables instead and restrict the operations on the view to queries.

Everytime, The action to take is to work on the underlying tables rather than the view so I guess there is no other way than deleting in each table first then in the master table.

To get the error message I use:

http://ora-01732.ora-code.com/

Type your error message in the prefix of the URL and you get explanatino + actions.

I will have to do with triggers, it seems there's no other way.

Report message to a moderator

Previous Topic:	Explicit and Implicit Cursors
Next Topic:	ORA-00937: not a single-group group function

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 02:29:57 CST 2024