Is it possible to delete records from multiple tables with a single SQL statement?
I have a membership application form and we are currently in testing mode. I want to write a cffunction that will, at the end of testing, identify all the test entries from the database (MS SQL) so the application can start fresh.
Currently, there are three tables where the information is stored, the Users, UserDetails, and UserWorkHistory. Each has a UserID column to associate the entries and the UserDetails table has a field called ApplicationTest, which is a boolean. I have another query / function to identify the UserID's generated for the test entries and am invoking them into the Delete Test Entries function.
I am including a code snippet of what I have so far. There are many articles stating I have to create three separate queries to delete the records from each table individually but it would seem there should be a way to delete from all three tables in a single SQL statement.
<cffunction name="getTestEntries" returntype="query">
<cfset var TestEntries = "">
<cfquery name="TestEntries" datasource="#application.datasource#">
WHERE ApplicationTest = 'true'
<cffunction name="DeleteTestEntries" output="no" hint="Delete Entries from Users, UserDetails and WorkHistories which are test entries">
<cfinvoke method="getTestEntries" returnvariable="TestEntries">
<cfquery name="DeleteEntries" datasource="#application.datasource#">
DELETE FROM Users, UserDetails, UserWorkHistory
WHERE (Users.UserID = UserDetails.UserID) AND (Users.UserID = UserWorkHistory.UserID) AND (UserDetails.ApplicationTest = '1');