Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 548
  • Last Modified:

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

0
perdiemconsulting
Asked:
perdiemconsulting
2 Solutions
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now