Solved

In SQL how do I delete records from two tables?

Posted on 2009-07-02
3
157 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
[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
  • 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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

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 …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

734 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