Delete record without leaving orphans

[turtle]

I have 2 MS access tables "links" and "linkCategory" where links get
assigned to a category for display.

I want to be able to delete a "linkCategory" record but there could be
"links" records associated with the category so I want to check if there are
any before deleting the category so that I don't leave orphan links.

Everything works when I do not query the links table but when I place a
query to find if there are any links associated with the linkCategory I get
the following error:

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC Microsoft Access Driver] Syntax error in string in query
expression 'linkCategoryID=4"'.


SQL = "select count(*) as liveLink from links where linkCategoryID=4""



Coding below (the query that is separated by blank lines is the problematic
one):


<cfif NOT IsDefined("form.deleteLinkCategory")>
<cfupdate datasource="#application.dsn#" tablename="linkCategory"
formfields="linkCategoryID, (remaining fields)......">
<cfelse>

<cfquery datasource="#application.dsn#" name="checkForLinks">
select count(*) as liveLink
from links
where linkCategoryID=#Val(linkCategoryID)#"
</cfquery>
<cfif #liveLink# is 0>

<cfquery name="DeleteLinkCategory" datasource="#application.dsn#">
delete from linkCategory
where linkCategoryID = #Val(linkCategoryID)#
</cfquery>
<cfelse>
<cfset #errorID#=2>
<cflocation url="./errorpage.cfm?#errorID#">
<cfabort>
</cfif>
</cfif>

[SafariTECH]

You cannot COUNT a wildcard, nor use wildcards in a query that contains COUNT
or other such aggregate functions.

Another option would be to create a custom view that shows related data in
both tables based on the ID - call your query against the custom View and all
data in either table should be removed.

[mxstu]

>You cannot COUNT a wildcard, nor use wildcards in a query that

>contains COUNT or other such aggregate functions.

Glen,

What do you mean by this? Did I miss something?

[Dan Bracuk]

Somebody missed something, possibly me. I thought it was the double quote after the number 4.

[mxstu]

>Somebody missed something, possibly me. I thought it was the double quote

>after the number 4.

If the double quote is in the actual code, then you're right that it might
cause a problem. I got side tracked by the wildcard comment ;-)

[The ScareCrow]

While I agree that the double quote is the problem, I just thought I would post
another solution other than having 2 queries.

delete
from linkCategory
where linkCategoryID Not In (Select linkCategoryID from links)
and linkCategoryID=#Val(linkCategoryID)#

Ken

[mxstu]

Ken,

I definitely prefer your "delete" query over the original version. It also
looks like the OP wants to return some type of status indicating the
success/failure of the delete operation. Since they are using Access I think
they may still need an additional query to get the final status of the
operation... even if it is only using something like @@ROWCOUNT.

It looks like there are a few other minor issues with the code, like a missing
query name, extra pound signs, etc.

<cfif #liveLink# is 0>

... should be ....

<cfif checkForLinks.liveLink eq 0>

.... and this .....


<cfelse>
<cfset #errorID#=2>
<cflocation url="./errorpage.cfm?#errorID#">
<cfabort>
</cfif>


...... should probably use a parameter name in the cflocation ....


<cfelse>
<cfset errorID =2>
<cflocation url="./errorpage.cfm?errorID=#errorID#">
<cfabort>
</cfif>

[The ScareCrow]

mxstu,

Yes, I agree. But it is hard to determine what the OP wants to do from this.
I actually do not like deleting records. While the OP has only listed 2
tables being linked here, in my experience it usually involves others. Eg you
might want to keep stats on the links clicked. Then if you delete, then you
have to delete them all and thus your stats go to crap.
I prefer to have an extra column (isActive) which is a bit column. This
allows for the column to be "deleted" (made not active), but still keep all the
records.

Ken

[mxstu]

Ken,

Absolutely. Using an IsActive or IsDeleted flag is they way to go IMO too.
With "logical" deletes you never have to worry about inadvertently hosing other
database information. Although, after a few hours scrambling through backups,
frantically searching for information that was deleted, but shouldn't have
been, most people become converts too. Experience is a good teacher... bad
experience is even better ;-)