Solved

In SQL how do I delete records from two tables?

Posted on 2009-07-02
3
152 Views
Last Modified: 2012-05-07
I have the attached table structure within my sql server 2005 database. I have written the attached stored procedure hoping it would allow me to delete records within my ApplicantInterviews table but I get an error on my foreign key value constraint (InterviewID) due to items being under this key. How can I alter my stored procedure to delete the interview in my ApplicantInterviews table aswell as the interviewers that come under that interview (InterviewID) in my ApplicantInterviewInterviewers table??

Thanks.
ALTER PROCEDURE [Applicant].[proc_DeleteApplicantInterview]

	

	@InterviewID int,

	@InterviewInterviewerID int

	

AS

	DELETE FROM Applicant.ApplicantInterviews

	WHERE InterviewID = @InterviewID

	

RETURN

Open in new window

database.jpg
0
Comment
Question by:Shepwedd
  • 2
3 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24764784


ALTER PROCEDURE [Applicant].[proc_DeleteApplicantInterview]
     
      @InterviewID int,
      @InterviewInterviewerID int
     
AS
      SET NOCOUNT ON
      DELETE FROM Applicant.ApplicantInterviewInterviewers
      WHERE InterviewID = @InterviewID

      DELETE FROM Applicant.ApplicantInterviews
      WHERE InterviewID = @InterviewID
     
RETURN
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24764792
Dont you want to maintain a History of this information before deleting this ?
0
 

Author Comment

by:Shepwedd
ID: 24764921
That worked! Thanks.

I would like to maintain a history but I'm hoping my users will only use the delete if they had entered incorrect data.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now