question

anitia avatar image
anitia asked

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

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)
tables
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Scot Hauder avatar image
Scot Hauder answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.