Delete statement for records with multiple FK

Question

Delete statement for records with multiple FK

anitia

SSC Rookie

Points: 45
More actions
July 9, 2011 at 6:31 pm

#240614

I am trying to delete records from a table that has multiple FK in Multiple tables. So I created a statement for each table. I was told I needed to do all of this in a statement instead of all seperate.
Below is an example of what I wrote. How do I write the below delete statement in one statement?
begin tran
delete CMA
from CMA
inner join CMNFormQst on CMA.CMQK = CMQ.CMQK
inner join cmnform on CMQ.CMK = CM.CMK
where cm.cmk in (302,303)
begin tran
delete CMQ
from CMQ
inner join cm on CMQ.CMK = CMN.CMK
where cmn.cmk in (302,303)
begin tran
delete PD
from PD
inner join cmnform on PD.CMK = CM.CMK
where cmn.cmk in (302,303)
begin tran
delete Activity
from Activity
inner join C(nolock) on activity.ck = c.ck
inner join cm on C.CMK = CM.CMK
where cmn.cmk in (302,303)

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 1

anitia (7/9/2011)
I am trying to delete records from a table that has multiple FK in Multiple tables. So I created a statement for each table. I was told I needed to do all of this in a statement instead of all seperate.
Below is an example of what I wrote. How do I write the below delete statement in one statement?
begin tran
delete CMA
from CMA
inner join CMNFormQst on CMA.CMQK = CMQ.CMQK
inner join cmnform on CMQ.CMK = CM.CMK
where cm.cmk in (302,303)
begin tran
delete CMQ
from CMQ
inner join cm on CMQ.CMK = CMN.CMK
where cmn.cmk in (302,303)
begin tran
delete PD
from PD
inner join cmnform on PD.CMK = CM.CMK
where cmn.cmk in (302,303)
begin tran
delete Activity
from Activity
inner join C(nolock) on activity.ck = c.ck
inner join cm on C.CMK = CM.CMK
where cmn.cmk in (302,303)

The only way to do it in one statement would be to change the foreign keys so that they cascade on delete. In this way you could simply delete the rows from the parent.

If that is not an option then you'll need to issue separate deletes for all child tables before deleting the parent table. If you go down that route I would recommend setting up proper transaction boundaries so that it acts as a single delete and if an error occurs all work can be rolled back.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Not on the delete but...

You have multiple begin tran statements and no commits. You need a commit transaction for each begin transaction. I would suggest rather a single begin tran at the beginning, the deletes in a try-catch and a commit/rollback at the end.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

anitia SSC Rookie Points: 45 More actions · Answer 3

Great. Thank you. I'll try that. I usually just keep commiting multiple times at the end of running all the statements.

But maybe that is what they meant. One begin tran at the beginning and then one commit at the end. I was thinking they wanted one long scipt but was having a difficult time finding out how to do that.

thanks so much!

anitia SSC Rookie Points: 45 More actions · Answer 4

Thank you. I'll try that. I was thinking they wanted it all in one script. But this makes more sense.

Thanks Again!