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

sorry, my crystal ball is temporarily down (it runs on microsoft windows)

do you mind sharing what the error message is?

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.

okay, i just checked the allowable syntax for the DELETE statement

i suggest you do the same

I did, and came up clueless - which is why I'm asking here.

okay, i'll walk you through it

there are two variations of the multiple table DELETE syntax --
Or:
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?

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...

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