Solved

In SQL how do I delete records from two tables?

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Table create permissions on SQL Server 2005 9 43
MS SQLK Server multi-part identifier cannot be bound 5 41
SQL Query with Sum and Detail rows 2 54
Query to return total 6 22
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

828 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