Results 1 to 8 of 8
  1. #1
    Join Date
    May 2003
    Posts
    6

    Unanswered: delete corresponding records in 4 tables (was "Newbie Query Problem")

    Hi

    I'm messing around with this query but for some reason it's not working.

    Any help would be appreciated:

    DELETE FROM zsmos_comprofiler c, zsmos_users u, zsmos_core_acl_aro a, zsmos_core_acl_groups_aro_map m WHERE c.id=u.id AND c.id=a.value AND
    m.aro_id=a.aro_id AND ((DATE_SUB(CURDATE(),INTERVAL 60 DAY) > u.registerDate)&& (u.lastvisitDate='0000-00-00 00:00:00')) AND c.confirmed = '0'

    I want to delete corresponding records in 4 tables linked as above - I've tested it as a SELECT query and that works ok - when I change it to a delete though it throws up an error.

    Help! Thanks

    Eddie

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, my crystal ball is temporarily down (it runs on microsoft windows)

    do you mind sharing what the error message is?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2003
    Posts
    6
    Sorry, I kinda assumed that it was a glaring error in my syntax

    Running MySQL 3.23.54 (don't ask).

    Error is:

    SQL-query :

    DELETE FROM zsmos_comprofiler c, zsmos_users u, zsmos_core_acl_aro a, zsmos_core_acl_groups_aro_map m WHERE c.id=u.id AND c.id=a.value AND m.aro_id=a.aro_id AND ((DATE_SUB(CURDATE(),INTERVAL 60 DAY) > u.registerDate)&& (u.lastvisitDate='0000-00-00 00:00:00')) AND c.confirmed = '0'

    MySQL said:

    You have an error in your SQL syntax near 'c, zsmos_users u, zsmos_core_acl_aro a, zsmos_core_acl_groups_aro_map m WHERE c.' at line 1

    Any ideas?

    thanks again.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, i just checked the allowable syntax for the DELETE statement

    i suggest you do the same
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2003
    Posts
    6
    I did, and came up clueless - which is why I'm asking here.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, i'll walk you through it

    there are two variations of the multiple table DELETE syntax --
    Code:
    DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
        tbl_name[.*] [, tbl_name[.*]] ...
        FROM table_references
        [WHERE where_condition]
    Or:
    Code:
    DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
        FROM tbl_name[.*] [, tbl_name[.*]] ...
        USING table_references
        [WHERE where_condition]
    now, one of these allows you to say DELETE FROM, but then you must use USING

    the other one does not use USING, but requires that you list the tables to be deleted from in between the word DELETE and the word FROM

    helps?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2003
    Posts
    6
    I've been through and through the MySQL manual already, I've also had a thorough scour around looking for an example that is anything like what I'm trying to do before posting here.

    Following the syntax prescribed, I ended up with:

    DELETE FROM zsmos_users,zsmos_comprofiler,zsmos_core_acl_aro, zsmos_core_acl_groups_aro_map USING zsmos_users, zsmos_comprofiler,zsmos_core_acl_aro, zsmos_core_acl_groups_aro_map WHERE zsmos_users.id=zsmos_comprofiler.id AND zsmos_comprofiler.id=zsmos_core_acl_aro.value AND zsmos_core_acl_groups_aro_map.aro_id=zsmos_core_ac l_aro.aro_id AND ((DATE_SUB(CURDATE(),INTERVAL 120 DAY) > zsmos_users.registerDate)&& (zsmos_users.lastvisitDate='0000-00-00 00:00:00')) AND zsmos_comprofiler.confirmed = '0'

    Which then threw up the error:

    SQL Query

    MySQL Said:

    You have an error in your SQL syntax near 'zsmos_users, zsmos_comprofiler,zsmos_core_acl_aro, zsmos_core_acl_groups_aro_map' at line 1

    I also tried the:

    DELETE zsmos_users,zsmos_comprofiler,zsmos_core_acl_aro, zsmos_core_acl_groups_aro_map FROM zsmos_users, zsmos_comprofiler,zsmos_core_acl_aro, zsmos_core_acl_groups_aro_map WHERE zsmos_users.id=zsmos_comprofiler.id AND zsmos_comprofiler.id=zsmos_core_acl_aro.value AND zsmos_core_acl_groups_aro_map.aro_id=zsmos_core_ac l_aro.aro_id AND ((DATE_SUB(CURDATE(),INTERVAL 120 DAY) > zsmos_users.registerDate)&& (zsmos_users.lastvisitDate='0000-00-00 00:00:00')) AND zsmos_comprofiler.confirmed = '0'

    method, which threw up the same error.

    So, unfortunately no, that doesn't help much...

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, wait a sec, i reviewed the thread and noticed what version you're on

    maybe you should have noticed too, eh

    i don't think your version supports multi-table deletes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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