SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Delete from multiple tables

    I'm trying to delete from 3 tables which share a primary key value for just one of these values
    Code:
    DELETE GAME_CONFIG_RULES,GAME_CONFIG_CIVILISATIONS,GAME_CONFIG_CIVILISATION_UNITS FROM GAME_CONFIG_RULES,GAME_CONFIG_CIVILISATIONS,GAME_CONFIG_CIVILISATION_UNITS WHERE GAME_CONFIG_CIVILISATIONS.game_config_id='2' OR GAME_CONFIG_RULES.game_config_id='2' OR GAME_CONFIG_CIVILISATION_UNITS.game_config_id='2'
    This deletes 0 rows despite there being rows in 2 of these tables, but not the third.

    Is it possible to do this in one query? Or do I just make 3 queries instead?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i would do it in 3 separate queries

    what you have there is dangerous and as i read it (without actually testing it) it would delete all rows from all three tables as soon as one of those WHERE conditions is true
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's the whole point

    I'm trying to delete all rows which have the same 'config_id' from those 3 tables.

    It should only delete the rows with config_id=$_POST[config_id] - but the above only works if there are rows in each table with that id, and there may not always be rows in each table with the required id.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by L4suicide View Post
    ... and there may not always be rows in each table with the required id.
    then use three separate DELETE queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by L4suicide View Post
    I'm trying to delete from 3 tables which share a primary key value for just one of these values
    Not sure what you mean but, is it that the records in the first table have a specific ID and in the other tables, the foreign key is the PK in table 1? Maybe this is where foreign key constraints set to ON DELETE CASCADE would delete the records in tables 2 and 3, where the match is made only on table 1?

    bazz


Bookmarks

Posting Permissions

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