Delete Record and All Related

[tlhawkins]

Hey Guys Need some help...

I have a database (Access) that has enforced referencial integerity all over the place. I need to be able to delete a record at a very high level and delete all records referenced to it as well. I know I can do this with several Queries and Several Deletes but I was hoping there was an easier way.

Thanks for your time.

 

[Silvano]

lets say that you have created a list that contains names of all tables in your database; in that case you can loop through the all tables using only one query:

<cfset allTables=&quot;table1,table2,table3,table4&quot;>

<cfloop index=&quot;tableName&quot; list=&quot;allTables&quot; delimiters=&quot;,&quot;>
<cfquery name=&quot;queryName&quot; database=&quot;dbname&quot;>
DELETE FROM #tableName#
WHERE file = '#fileID#';
</cfquery>
</cfloop> Sylvano
dsylvano@hotmail.com

 

[tlhawkins]

I'm sure that would work in some cases.

Let me be a little more clear.

Table1
ID
Name
Age

Table2
ID
ref_Table1_ID
Job

Table3
ID
ref_Table2_ID
Phone

Where table2.ref_table1_ID = table1.id
AND table3.ref_table2.ID = table2.ID


There are a lot more fields then that but what if I want to erase Everything Starting from Table1.Id=2 and continuing through table3.

Can I Inner Join with a Delete?

Sounds scarey.