query problem?

[thenotorious_flag]

I am trying to run the values to delete from database but

error occurs as:
Error Executing Database Query.
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 'JOIN activities_attachments ON tblactivities.activityID = activities' at line 2

The error occurred in D:\home\ecolife.in\wwwroot\admin\CFC\activities.cfc: line 127
Called from D:\home\ecolife.in\wwwroot\admin\activities\index.cfm: line 43
Called from D:\home\ecolife.in\wwwroot\admin\activities\index.cfm: line 33
Called from D:\home\ecolife.in\wwwroot\admin\activities\index.cfm: line 12
Called from D:\home\ecolife.in\wwwroot\admin\activities\index.cfm: line 1
Called from D:\home\ecolife.in\wwwroot\admin\CFC\activities.cfc: line 127
Called from D:\home\ecolife.in\wwwroot\admin\activities\index.cfm: line 43
Called from D:\home\ecolife.in\wwwroot\admin\activities\index.cfm: line 33
Called from D:\home\ecolife.in\wwwroot\admin\activities\index.cfm: line 12
Called from D:\home\ecolife.in\wwwroot\admin\activities\index.cfm: line 1

125 : OUTER JOIN
126 : activities_attachments ON tblactivities.activityID = activities_attachments.Attach_ActivityID
127 : WHERE activityID IN (<cfqueryparam cfsqltype="cf_sql_numeric" value="#trim(arguments.theIDNodes)#" list="yes">)
128 : </cfquery>
129 : </cffunction>

This is the query i am running,
DELETE FROM tblactivities
JOIN activities_attachments ON tblactivities.activityID = activities_attachments.Attach_ActivityID
WHERE activityID IN (<cfqueryparam cfsqltype="cf_sql_numeric" value="#trim(arguments.theIDNodes)#" list="yes">)

What i am doing is selecting the values from tableactivities and then see if the activityfield value is equal to attach_activityID, then it shud delete it

but it is throwing an error?

Can someone please guide what i am doing wrong?

regards

[Newsgroup_User]

multiple-table delete syntax in mysql is:

either

DELETE table1, table2, ..., tableN
FROM [JOIN statements here]
WHERE ...

or

DELETE
FROM table1, table2, ..., tableN
USING [JOIN statements here]
WHERE ...

more info, if you need it, is in chapter 13.2.1 of mysql reference manual

hth


Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Dan_Bracuk]

Use a subquery instead of a join.