SitePoint Sponsor

User Tag List

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

    Multi Table Deletes

    I'm trying (once again) to delete from multiple tables which are linked by the same primary id.

    Code:
    DELETE
    GAME_CONFIG_INITIAL_BUILDINGS.*,GAME_CONFIG_INITIAL_LAND.*,GAME_CONFIG_INITIAL_RESOURCES.* 
    FROM 
    GAME_CONFIG_INITIAL_BUILDINGS,GAME_CONFIG_INITIAL_LAND,GAME_CONFIG_INITIAL_RESOURCES 
    WHERE GAME_CONFIG_INITIAL_LAND.game_config_id=GAME_CONFIG_INITIAL_RESOURCES.game_config_id AND GAME_CONFIG_INITIAL_RESOURCES.game_config_id=GAME_CONFIG_INITIAL_BUILDINGS.game_config_id AND GAME_CONFIG_INITIAL_RESOURCES.game_config_id='1'
    What I have works, but looking at the manual I think there is an easier way with the 'USING' clause. I just can't get it to work like the syntax suggests:

    DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]
    Am I reading this wrong?
    Is there a quicker way of getting what I want done without so much garbage in my clause?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    there are three ways

    1. delete individually from each table one at a time
    2. use cascading deletes (requires innodb and foreign keys)
    3. use a multi-table joined delete

    you're working on the 3rd, and the part you're missing is that the word DELETE must be followed immediately by the word FROM (unless you want one of those optional keywords), and the USING clause is where you do the joins
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

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
  •