Delete using Multiple Joins?

Is it possible to do a delete on a multiple join?  Here is a example:

begin transaction
USE AdminTool
      DELETE
      reg.*,
      rt.registrant_type_name,
      rf.registrants_forms_timestamp,
      f.formID, f.form_name,
      r.*
      from
            SLP_1380_REGISTRANTS_TBL reg
      left outer join
            SLP_1380_REGISTRANTS_FORMS_TBL rf
            on reg.registrantID = rf.registrantID
      left outer join
            SLP_1380_FORMS_TBL f
            on rf.formID = f.formID
      left outer join
            SLP_1380_REGISTRANTS_FORMS_RESPONSES_TBL rfr
            on rf.registrants_forms_ID = rfr.registrants_forms_ID
      left outer join
            SLP_1380_RESPONSES_TBL r
            on rfr.responseID = r.responseID
      left outer join
            SLP_1380_REGISTRANT_TYPES_TBL rt
            on reg.registrant_typeID = rt.registrant_typeID
      WHERE emailAddress LIKE '%@myWebSite.com';

Commit transaction


Is the above the proper syntax?  Thanks for the help!

Terry
LVL 1
splendorxAsked:
Who is Participating?
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
You cannot delete from multiple tables in a single query; however, if you have cascaded delete referential integrity enabled records in child tables will be delete when the parent record is deleted.
0
 
Brian CroweDatabase AdministratorCommented:
DELETE SLP_1380_REGISTRANTS_TBL
     from
          SLP_1380_REGISTRANTS_TBL reg
     left outer join
          SLP_1380_REGISTRANTS_FORMS_TBL rf
          on reg.registrantID = rf.registrantID
     left outer join
          SLP_1380_FORMS_TBL f
          on rf.formID = f.formID
     left outer join
          SLP_1380_REGISTRANTS_FORMS_RESPONSES_TBL rfr
          on rf.registrants_forms_ID = rfr.registrants_forms_ID
     left outer join
          SLP_1380_RESPONSES_TBL r
          on rfr.responseID = r.responseID
     left outer join
          SLP_1380_REGISTRANT_TYPES_TBL rt
          on reg.registrant_typeID = rt.registrant_typeID
     WHERE emailAddress LIKE '%@myWebSite.com';
0
 
ptjcbConnect With a Mentor Commented:
It is possible to have joins in a DELETE statement. But you can not delete from multiple tables. You will have to write multiple statements.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Scott PletcherConnect With a Mentor Senior DBACommented:
Which column is emailAddress in?  You don't need the joins to any table except ones related to getting the emailAdress.  And with LEFT JOINs, you'll include *every* row in the main table that passes the WHERE, regardless of matching, or not, on the other tables.
0
 
splendorxAuthor Commented:
The email address field is located in SLP_1380_REGISTRANTS_TBL.  This query is for a MS SQL database.  Is there a preferred optimal way of deleting records from multiple tables?  With seperate delete statements or by using cascaded delete referential integrity enabled.  I have never used cascading delete in MS SQL - only in MS Access.  Not sure how effective it may be...
0
 
Brian CroweDatabase AdministratorCommented:
When the CASCADE ON DELETE constraint is enforced on a foreign key reference the records in a child table are delete whenever the parent record is deleted.  In your example it appears as if SLP_1380_REGISTRANTS_TBL is the parent table for the SLP_1380_REGISTRANTS_FORMS_TBL table and the SLP_1380_REGISTRANT_TYPES_TBL table.  In this case if the CASCADE constraint is in effect deleteing a record from the REGISTRANTS table would automatically delete any rows in the FORMS and TYPES tables that referenced that record.  You could continue this pattern on down the chain to the child tables of TYPES and FORMS...etc.

The other option is to delete from each table individually.  If there are foreign key relations between the tables as it appears then you have to delete from the bottom up...meaning that you need to delete the records from the child tables before moving to the parent tables or you will get an error.
0
 
splendorxAuthor Commented:
Thanks for the help!
0
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.

All Courses

From novice to tech pro — start learning today.