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

Store the Id's to delete in a temp table or table variable, like

declare @toDelete as table (id int);

insert into @toDelete (id)
Select Id 
FROM tbl_AutoSize
WHERE 1=1
    AND DepartmentId = @PAR_DepartmentId
    AND SizePot = @PAR_SizePot
    AND ProductSize = @PAR_ProductSize

DELETE From tbl_AutoSizeDetail 
where AutoSizeId in (select id from @toDelete);

DELETE FROM tbl_AutoSize
where Id in (select id from @toDelete);   

Answer 3

below is sql script but require refactoring

Not clear what you mean or where the issue is or in which way you want to refactor the query? May can you explain it more detailed, please.
Or better post table design as DDL, some sample data as DML statement and the expected result = which rows to delete.

Answer 4

So a simple solution to the problem is this:

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

That is, delete from the parent first.

To achieve this, you would set up the FK Constraint this way:

ALTER TABLE tbl_AutoSizeDetail ADD
   CONSTRAINT fk__tbl_AutoSizeDetail_tbl_AutoSize
   FOREIGN KEY (AutoSizeId) REFERENCES tbl_AutoSize(AutoSizeID)
   ON UPDATE CASCADE ON DELETE CASCADE

Cascading deletes are not always the right thing, and in the wrong place it can cause big accidents. (For instance if the Orders table has an FK reference to Customers, it should not have ON CASCADE DELETE!)

Answer 5

Hi @Shabbir Daruwala ,

You could refer other experts' suggestions.

Please also consider to create one delete trigger below:

CREATE trigger MY_DELETE_trigger_tbl_AutoSizeDetail  
on tbl_AutoSizeDetail  
after delete   
as   
begin  
DELETE A  
FROM tbl_AutoSize A  
INNER JOIN DELETED B  
    ON A.ID=B.AutoSizeId  
end;  

Then you only need to perform below first delete command.

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

Finally your tbl_AutoSize related data would be deleted automatically.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.