Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

delete a record that has a foreign key

Posted on 2014-12-15
1
Medium Priority
?
83 Views
Last Modified: 2014-12-16
I have the following SQL JOIN statement

select * from tblOrgGoals G
Inner Join tblOrgObjectives O on G.GoalID = O.GoalID
Inner Join tblOrgPI P on O.ObjectiveID = P.ObjectiveID

If I would like to delete a record from tblOrgGoals and it is linked to the other two tables, how would I be able to cascade delete all the records that record has a relationship with?

Can I also delete a record from tblOrgPI and cascade delete in the other direction?
0
Comment
Question by:al4629740
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 40501038
Assuming you know the GoalID (42 here), you could delete the row(s) from the related table, then the goal table.  
All in a transaction so the set of deletes succeed or fail together.
BEGIN TRY
  BEGIN TRAN tr

  DELETE P
  FROM tblOrgPI P
     Join tblOrgObjectives O ON O.ObjectiveID = P.ObjectiveID
  WHERE O.GoalID = 42

  DELETE O
  FROM tblOrgObjectives O
  WHERE O.GoalID = 42

  DELETE FROM tblOrgGoals WHERE GoalID=42 

  -- If code execution makes it here, good to go
  COMMIT TRAN tr
END TRY

BEGIN CATCH
  -- Ruh roh.  An error occured. 
  PRINT 'An error occured'
  ROLLBACK TRAN tr
END CATCH

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

636 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