x

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) 
more ▼

asked Jul 10, 2011 at 04:32 AM in Default

avatar image

anitia
13 1 1 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Jul 10, 2011 at 09:50 AM

avatar image

Scot Hauder
6.4k 13 16 22

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x61

asked: Jul 10, 2011 at 04:32 AM

Seen: 894 times

Last Updated: Jul 10, 2011 at 01:09 PM

Copyright 2016 Redgate Software. Privacy Policy