SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Barefoot on the Moon! silver trophy Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,606
    Mentioned
    56 Post(s)
    Tagged
    1 Thread(s)

    Confused about DELETE syntax

    I'm not sure if I'm using this delete statment or not. I have 7 tables set up where each row has a unique ID number, which matches up with rows in other tables. What I want to do is delete all of those row from all the tables that match the unique ID number.

    Here's the statement I tried:

    Code:
    DELETE FROM maintable, storage, arrangement, format, textareas, record_retention, record_status WHERE maintable.UID=3 AND storage.UID_ref=3 AND arrangement.UID_ref=3 AND format.UID_ref=3 AND textareas.UID_ref=3 AND record_retention.UID_ref=3 AND record_status.UID_ref=3
    Here's the error I got:
    Code:
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE maintable.UID=3 AND storage.UID_ref=3 AND arrangement.UID_ref=3 AND format' at line 1
    Any ideas?

  2. #2
    Sell crazy someplace else markl999's Avatar
    Join Date
    Aug 2003
    Location
    Manchester, UK
    Posts
    4,007
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Multi table delete syntax is:
    DELETE maintable.*, storage.*, arrangement.*, format.*, textareas.*, record_retention.*, record_status.* FROM maintable, storage, arrangement, format, textareas, record_retention, record_status WHERE maintable.UID=3 AND storage.UID_ref=3 AND arrangement.UID_ref=3 AND format.UID_ref=3 AND textareas.UID_ref=3 AND record_retention.UID_ref=3 AND record_status.UID_ref=3

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

  3. #3
    SitePoint Enthusiast shn's Avatar
    Join Date
    Jan 2006
    Location
    Munich DE
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You're using the single table syntax. For multi-table operations you will need table_references. See MySQL manual for more information. (It's 5.0 manual but this issue is same as of MySQL >=3.23)
    Patrick

  4. #4
    Barefoot on the Moon! silver trophy Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,606
    Mentioned
    56 Post(s)
    Tagged
    1 Thread(s)
    Thanks guys, that was the problem


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
  •