Delete sub record (different table) if exists

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Delete sub record (different table) if exists

Author

Topic

SpeshulK926
Starting Member

16 Posts

Posted - 2014-05-25 : 16:31:55

I'm having a hard time wrapping my brain around this and hoping someone can help. I know how to grab an ID from a row if I am deleting 1 record so that I can delete the record that goes with it, but I'm not sure how to do it when deleting multiple records.

Below is my code for Deleting my multiple records:


ALTER PROCEDURE [dbo].[ap_DeletePlans]

	@Login_ID int,
	@PlanGuid varchar(36)

AS
	BEGIN
		DELETE FROM Plans
		WHERE [Login_ID] = @Login_ID AND [PlanGuid] = @PlanGuid
	END

My Plans table has a field called SubPlan_ID that points to a table called SubPlans that has an identical field (how they are matched) that is the Primary Key / Identifier. What I need to do is when I delete all records from Plans I need it to delete a record (if it exists) from SubPlans too.

Edit: Thought it might be easier to read if I post my columns in both tables that are relevant.


Plans
-------------
Login_ID      Plan_ID      PlanGuid      SubPlan_ID   ...More unrelated fields
                                            |
                                            |
SubPlans                                    |
-------------                               |
Login_ID      SubPlans_ID   <----------------

rocknpop
Posting Yak Master

201 Posts

Posted - 2014-05-26 : 01:27:07

You can have a foreign key on SubPlans table with an On Delete Cascade.
This way any record deleted in the parent table will auto delete the child table records too.

--------------------
Rock n Roll with SQL

SpeshulK926
Starting Member

16 Posts

Posted - 2014-05-26 : 10:39:41

quote:
Originally posted by rocknpop

You can have a foreign key on SubPlans table with an On Delete Cascade.
This way any record deleted in the parent table will auto delete the child table records too.

--------------------
Rock n Roll with SQL

Won't let me do that because it's not a mandatory field. It only creates a SubPlan if it needs it, so not all Plans have a SubPlan. It is NULL if there is no SubPlan

Edit: I have gotten something working, but it is not very efficient way to do it as I assume this is doing a table scan.


  BEGIN
    DELETE FROM SubPlans WHERE SubPlan_ID = (SELECT SubPlan_ID FROM Plans WHERE PlanGuid = @PlanGuid AND SubPlan_ID > 0)
  END
  DELETE FROM Plans
    WHERE [Login_ID] = @Login_ID AND [PlanGuid] = @PlanGuid

rocknpop
Posting Yak Master

201 Posts

Posted - 2014-05-26 : 23:49:06

1. If Plans.Subplan_ID is Unique then you can define a unique constraint on it and a foreign key with On delete cascade on the SubPlans table pointing to this parent.

2. If you can't do point# 1 above, then I guess your only option is to write two separate deletes, for deleting child:

DECLARE @subPlanID INT = (SELECT SubPlan_ID FROM Plans WHERE PlanGuid = @PlanGuid AND ISNULL(SubPlan_ID,0)>0)
IF @subPlanID IS NOT NULL
DELETE FROM SubPlans WHERE SubPlan_ID = @subPlanID

--------------------
Rock n Roll with SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources