Delete records from multiple tables

Posted on 2008-11-12
Last Modified: 2012-05-05
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'


    	<cfreturn TestEntries>



    <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');


        <cfreturn true>


Open in new window

Question by:perdiemconsulting
    LVL 7

    Assisted Solution

    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.
    LVL 10

    Accepted Solution

    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 )

    ALTER TABLE UserWorkHistory  ADD
        CONSTRAINT fk_UserWorkHistory_User FOREIGN KEY (UserID)

    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.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
    I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now