mysql error in multi-delete

[newchickinCF]

I am running a multi delete but an error while executing it:

i using this query:

DELETE FROM FaqManager_categories.*, FaqManager_articles.*
USING
FaqManager_categories as fc1
INNER JOIN FaqManager_articles as fa1
WHERE
fc1.category = fa1.category
AND
fc1.category =
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(arguments.catName)#" maxlength="255">

and then i am getting this error:

Error Executing Database Query.
Unknown table 'faqmanager_categories' in MULTI DELETE

The error occurred in C:\Inetpub\wwwroot\cfc\admin.cfc: line 147
Called from C:\Inetpub\wwwroot\makechange.cfm: line 26
Called from C:\Inetpub\wwwroot\cfc\admin.cfc: line 147
Called from C:\Inetpub\wwwroot\makechange.cfm: line 26

145 : AND
146 : FaqManager_articles.Category =
147 : <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(arguments.catName)#" maxlength="255">
148 : </cfquery>
149 : </cffunction>

SQLSTATE 42S02
SQL DELETE FROM FaqManager_categories.*, FaqManager_articles.* USING FaqManager_categories as fc1 INNER JOIN FaqManager_articles as fa1 WHERE fc1.category = fa1.category AND FaqManager_articles.Category = (param 1)
VENDORERRORCODE 1109
DATASOURCE faqs
Resources:

[Dan_Bracuk]

on delete queries, just name the tables - you don't have to specify fields.

[newchickinCF]

i changed the code to reflect

DELETE FROM FaqManager_categories
USING FaqManager_categories
RIGHT JOIN FaqManager_articles
ON FaqManager_categories.category = FaqManager_articles.category
WHERE
FaqManager_categories.category = <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(arguments.catName)#" maxlength="255">

what it did, it deleted the record from the faqmanager_categories table and did not removed the matching the matching record from faqmanager_articles.

can anyone point what i am doing wrong.

Thanks

[Dan_Bracuk]

Do you have a foreign key between categories and articles?

[newchickinCF]

no i don't have foreign key relation between them. also category field of both tables are independant, i mean category is neither a primary key nor foreign key

[Dan_Bracuk]

The simplest way to achieve this is with two queries, one for each table.

[newchickinCF]

Thanks that the last method i adopted but

it has some limitations although i have to write 2 invoke statement which took some time to delete.

Thanks

[newchickinCF]

Thanks Dan for your quick replies. But The above solved my proble. but Now i have stuck at a point where i need to delete a record for articles table and relevant records from comments table depending on the fileID in both tables.

well the FileID is primary key in articles table and FILEID is refrenced in comments table but as an index not s a foreign key. I try making it forien but it generates various error: like foreign key mismatch, etc.

I did write a query which execute successfully but does delete the records from the comments table the query is as under:

DELETE articles
FROM articles
RIGHT JOIN
comments
ON articles.fileID = comments.fileID
where
articles.FileID IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#trim(arguments.fileID)#" list="yes">)

[Dan_Bracuk]

quote:

---

Originally posted by: newchickinCF
Thanks Dan for your quick replies. But The above solved my proble. but Now i have stuck at a point where i need to delete a record for articles table and relevant records from comments table depending on the fileID in both tables.

well the FileID is primary key in articles table and FILEID is refrenced in comments table but as an index not s a foreign key. I try making it forien but it generates various error: like foreign key mismatch, etc.

I did write a query which execute successfully but does delete the records from the comments table the query is as under:

DELETE articles
FROM articles
RIGHT JOIN
comments
ON articles.fileID = comments.fileID
where
articles.FileID IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#trim(arguments.fileID)#" list="yes">)

---

I may not use the same db software as you, so my syntax might not work in whatever you are using.

Your failure to add a foreign key was probably due to orphan records. You can deal with this two ways. One is to add an article record that doesn't represent an article, and use that id for your orphan comments records. Or, you can delete the orphan records.

The where clause of your update or delete query will be
where fileid in (
select fileid from comments
except
select fileid from articles
)

If you successfully establish a foreign key, you may be able to set up cascading deletes at the db level. I never bother. I run a delete query for each table. With two tables, it may or may not be worthwhile to do this inside cfloop list="comments,articles" index="thetable"

Also, your syntax was wrong. It's not
delete articles from articles
it's
delete from articles.

[newchickinCF · Accepted solution]

Thanks i did actually created a foreign key reference and that solved