Delete records from multiple tables

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#">
            SELECT UserID
            FROM UserDetails
            WHERE ApplicationTest = 'true'
            </cfquery>
    	<cfreturn TestEntries>
    
    </cffunction>
    <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');
            </cfquery>
        <cfreturn true>
    </cffunction>

Open in new window

perdiemconsultingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

black0psCommented:
I don't know about deleting from one single sql statement, but I would just do something like:


DELETE FROM Users WHERE UserID = 1;
DELETE FROM UserDetails WHERE UserID = 1;
DELETE FROM UserWorkHistory WHERE UserID = 1;

You could also setup the Primary Key in your SQL Database to make updates and deletes persistant so you only have to delete the User and the other records will delete also.
0
MauseCommented:
You should let the database take care of that when you delete a user from the user table.

You can make a cascade delete on the UserDetail and UserWorkHistory tables like this (only execute one time):

ALTER TABLE UserDetails ADD
    CONSTRAINT fk_UserDetails_User FOREIGN KEY (UserID )
        REFERENCES Users (UserID) ON CASCADE DELETE

ALTER TABLE UserWorkHistory  ADD
    CONSTRAINT fk_UserWorkHistory_User FOREIGN KEY (UserID)
        REFERENCES Users (UserID) ON CASCADE DELETE

When you add the cascade delete on the tables you only have to delete the record form the user table (delete form users where userID = 123) and all related record in userDetail and userWorkhistory will also be deleted.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.