Delete from Child than from Parent table using SQL Query

Question

Hi All,

How to delete from Child table and than from parent table with same filter, below is sql script but require refactoring

DELETE From tbl_AutoSizeDetail where AutoSizeId in (Select Id from  FROM tbl_AutoSize
                        WHERE 1=1
                        AND DepartmentId = @PAR_DepartmentId
                        AND SizePot = @PAR_SizePot
                        AND ProductSize = @PAR_ProductSize)



DELETE FROM tbl_AutoSize
                        WHERE 1=1
                        AND DepartmentId = @PAR_DepartmentId
                        AND SizePot = @PAR_SizePot
                        AND ProductSize = @PAR_ProductSize

Answer 1

What I would do is delete with a join to the parent table and output the deleted parentIds into a table variable. Then you can delete from the parent table using those Ids.

One time only I'm providing the DDL and data sample, but it should be you the next time. Put in some work if you expect people to help.

create table dbo.tbl_AutoSize
(
    Id int identity(1,1) primary key
    , DepartmentId int not null
    , SizePot int not null
    , ProductSize int not null
)

create table dbo.tbl_AutoSizeDetail
(
    Id int identity (1,1) primary key
    , AutoSizeId int not null references tbl_AutoSize(id)
    , Filler char(10) not null
)


insert into dbo.tbl_Autosize with (tablockx)
(DepartmentId, SizePot, ProductSize)
values
( 1, 1, 1)
, ( 2, 2, 2)


insert into dbo.tbl_AutoSizeDetail with (tablockx)
(
    AutoSizeId, Filler
)
values
(1, '1a')
, (1, '1b')
, (2, '2a')

go
/* before */
select 
    * 
from dbo.tbl_AutoSizeDetail as tasd
    join dbo.tbl_AutoSize as tas
        on tas.Id = tasd.AutoSizeId

declare 
    @PAR_DepartmentId int = 1
    , @PAR_SizePot int = 1
    , @PAR_ProductSize int = 1

declare @IdsForDeletion table (Id int)

begin transaction
    delete tasd
    output deleted.AutoSizeId into @IdsForDeletion
    from dbo.tbl_AutoSizeDetail as tasd
    join dbo.tbl_AutoSize as tas
        on tas.Id = tasd.AutoSizeId
    where 
        tas.DepartmentId = @PAR_DepartmentId
        AND tas.SizePot = @PAR_SizePot
        AND tas.ProductSize = @PAR_ProductSize

    delete tas
    from dbo.tbl_AutoSize as tas
    where exists
    (
        select * from @IdsForDeletion ifd
        where ifd.Id = tas.Id
    )
commit transaction

/*after */
select 
    * 
from dbo.tbl_AutoSizeDetail as tasd
    join dbo.tbl_AutoSize as tas
        on tas.Id = tasd.AutoSizeId

Answer 2

Great thanks everyone, I accept TOMZika as best solution.

Thanks everyone for your help.