delete records from a table that has multiple FK in Multiple tables

Question

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)

Answer 1

0

When they told you one statement I think they meant one transaction. You were on the right track, but use only one BEGIN TRAN at the top, do your multiple delete statements then do one COMMIT or ROLLBACK TRAN at the bottom after all of the delete statements.

answered Jul 10 '11 at 09:50 AM

Scot Hauder
5.7k ● 13 ● 15 ● 18

(comments are locked)

delete records from a table that has multiple FK in Multiple tables

Follow this question

Related Questions