sql - DELETE rows with foreign constraints without using "on cascade" -

sql - DELETE rows with foreign constraints without using "on cascade" -

i newb sql since can away simple queries/migrations without digging it, have more complex 1 doesn't involve bring together , wondering how delete when there foreign constraint without using on cascade. here's query. not find reply googling.

with adeletevariable (select top 1 fooid id [bsystem].[foo].[bar] order createtimeutc desc) delete [bsystem].[foo].[foobar] fooid = id delete [bsystem].[foo].[bar] fooid = id

so need delete [bsystem].[foo].foobar first, [bsystem].[foo].[bar] executed @ run time cannot hard coded. "1" going n, have code worked out.

i invalid column name 'id' when trying execute query. how can prepare it?

your delete statements don't reference adeletevariable. id column exists in context of adeletevariable.

you need bring together adeletevariable delete statements. also, can't reuse mutual table expression, have duplicate it.

see sql fiddle (with simplified schema/table names), below want.

with adeletevariable (select top 1 fooid id [bsystem].[foo].[bar] order createtimeutc desc) delete fb [bsystem].[foo].[foobar] fb inner bring together adeletevariable dv on (fb.fooid = dv.id); adeletevariable (select top 1 fooid id [bsystem].[foo].[bar] order createtimeutc desc) delete b [bsystem].[foo].[bar] b inner bring together adeletevariable dv on (b.fooid = dv.id);

of course, joining adeletevariable, don't need id alias. next work.

with adeletevariable (select top 1 fooid [bsystem].[foo].[bar] order createtimeutc desc) delete fb [bsystem].[foo].[foobar] fb inner bring together adeletevariable dv on (fb.fooid = dv.fooid); adeletevariable (select top 1 fooid [bsystem].[foo].[bar] order createtimeutc desc) delete b [bsystem].[foo].[bar] b inner bring together adeletevariable dv on (b.fooid = dv.fooid);

given there 2 separate mutual table expressions, there race condition, , sec look might homecoming different fooid (or multiple when adapt n records). might want select ids table variable instead of using mutual table expression. next should work.

declare @deletevariable table (fooid int); insert @deletevariable (fooid) select top 1 fooid [bsystem].[foo].[bar] order createtimeutc desc; delete fb [bsystem].[foo].[foobar] fb inner bring together @deletevariable dv on (fb.fooid = dv.fooid); delete b [bsystem].[foo].[bar] b inner bring together @deletevariable dv on (b.fooid = dv.fooid);

sql tsql with-statement