Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2012
    Posts
    91

    Unanswered: Delete from more than one table with one query

    Can I use a one query to delete data from more than one table?
    Deletes data from several tables using these queries:
    "DELETE FROM IDOP WHERE _ID_OPIS='" + Indeks + "'";
    "DELETE FROM IDKON WHERE _ID_OPIS='" + Indeks + "'";

    I am trying to send query like this:
    "DELETE FROM IDOP, IDKON, IDZZZ, IDCN, IDWARTOSC, IDOPIS, IDNAD, IDODB, IDNETTO, NAD, ODB, IDWARSTAT WHERE _ID_OPIS='" + Indeks + "'";
    This method results in an error.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Have you tried using a stored procedures where you can define all the deletes you want inside the stored procedure and you make one call to the stored procedure. For example:

    Code:
    CALL deleteTables();
    Where deleteTables is a stored procedure which includes code as follows:

    Code:
    CREATE PROCEDURE deleteTables(IN pIndexks VARCHAR(100))
    BEGIN
    DELETE FROM IDOP WHERE _ID_OPIS = pIndexks;
    DELETE FROM IDKON  WHERE _ID_OPIS = pIndexks;
    DELETE FROM IDZZZ WHERE _ID_OPIS = pIndexks;
    ...
    END;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jan 2012
    Posts
    91
    Quote Originally Posted by it-iss.com View Post
    Have you tried using a stored procedures where you can define all the deletes you want inside the stored procedure and you make one call to the stored procedure. For example:

    Code:
    CALL deleteTables();
    Where deleteTables is a stored procedure which includes code as follows:

    Code:
    CREATE PROCEDURE deleteTables(IN pIndexks VARCHAR(100))
    BEGIN
    DELETE FROM IDOP WHERE _ID_OPIS = pIndexks;
    DELETE FROM IDKON  WHERE _ID_OPIS = pIndexks;
    DELETE FROM IDZZZ WHERE _ID_OPIS = pIndexks;
    ...
    END;
    Thanks. So in the stored procedure must also specify all the tables from which I want to delete data. I meant more to reduce the number of typed queries.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if the tables are properly related with primary and foreign keys, and the foreign keys have been declared with ON DELETE CASCADE, then you should be able to delete all related rows in all tables just by deleting the row in the main table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2012
    Posts
    91
    Quote Originally Posted by r937 View Post
    if the tables are properly related with primary and foreign keys, and the foreign keys have been declared with ON DELETE CASCADE, then you should be able to delete all related rows in all tables just by deleting the row in the main table
    Can I change the already existing tables so as to indicate which column is foreign key? This is an example of how I created the tables:
    Code:
    CREATE TABLE IDOP
    (OPID INTEGER AUTO_INCREMENT,
    _ID_OPIS INTEGER,
    RODZAJ VARCHAR(2) NOT NULL default, 
    UNIQUE KEY OPID (OPID))
    Foreign key is "_ID_OPIS"

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in order for ON DELETE CASCADE to work, the tables have to be InnoDB

    and that table you showed, where _id_opis is the foreign key, what table does it reference?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2012
    Posts
    91
    Quote Originally Posted by r937 View Post
    in order for ON DELETE CASCADE to work, the tables have to be InnoDB

    and that table you showed, where _id_opis is the foreign key, what table does it reference?
    I have not explicitly specified key in the table SKLAD to which relates _ID_OPIS. Table SKLAD creates records as the first table and rewrite numerical _ID of the table to other tables to _ID_OPIS key. Table TABLE is my primary table.
    Code:
    CREATE TABLE SKLAD
    ( _ID INTEGER AUTO_INCREMENT,
    .............................................
    UNIQUE KEY _ID (_ID))

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by duf View Post
    Table TABLE is my primary table.
    could you clarify this please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2012
    Posts
    91
    Quote Originally Posted by r937 View Post
    could you clarify this please?
    I was wrong, sorry. Main table SKLAD.
    http://vlep.pl/no4x8v.jpg
    The concept is more my own than with a specific meaning. When I entered the data to database SKLAD is in the first table I make a record. And as I wrote, the other tables type a value _ID to the _ID_OPIS key.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, this should work...
    Code:
    ALTER TABLE idop
    ADD CONSTRAINT idop_sklad
        FOREIGN KEY ( _id_opis )
        REFERENCES sklad ( _ id )
        ON DELETE CASCADE ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2012
    Posts
    91
    Quote Originally Posted by r937 View Post
    okay, this should work...
    Code:
    ALTER TABLE idop
    ADD CONSTRAINT idop_sklad
        FOREIGN KEY ( _id_opis )
        REFERENCES sklad ( _ id )
        ON DELETE CASCADE ;
    Query passed without difficulty. But in the table IDOP do not see any significant changes. All column names remain the same and the number is as before. Does it look like this?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    adding a foreign key doesn't change any of the data

    try deleting a row from sklad and check to see that the same id was deleted from idop
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jan 2012
    Posts
    91
    Quote Originally Posted by r937 View Post
    adding a foreign key doesn't change any of the data

    try deleting a row from sklad and check to see that the same id was deleted from idop
    Super!. Thanks

Posting Permissions

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