Solved

In SQL how do I delete records from two tables?

Posted on 2009-07-02
3
156 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

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

749 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