• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

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
0
splendorx
Asked:
splendorx
3 Solutions
 
Brian CroweCommented:
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 CroweCommented:
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
 
ptjcbCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Scott PletcherSenior 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 CroweCommented:
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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

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