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

anitia gravatar image

anitia
13 1 1 1

(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

Scot Hauder gravatar image

Scot Hauder
6k 13 15 18

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x39

asked: Jul 10, 2011 at 04:32 AM

Seen: 715 times

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