asked on

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

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brian Crowe

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

SOLUTION

ptjcb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

splendorx

ASKER

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

Brian Crowe

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.

splendorx

ASKER

Thanks for the help!