question

ShabbirDaruwala-7855 avatar image
0 Votes"
ShabbirDaruwala-7855 asked TomZika commented

Delete from Child than from Parent table using SQL Query

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
sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomZika avatar image
1 Vote"
TomZika answered TomZika commented

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
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.



One time only I'm providing the DDL and data sample, but it should be you the next time.

True 136719-image.png +1

Could be great if all supporters tried to keep this rule and make the OP always do his part :-)

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.

Good approach. Just confirm it do the job. Might provide better performance than using three queries (separate query to fill the @toDelete)




1 Vote 1 ·
image.png (502 B)

I think that this script will not delete some of the rows from tbl_AutoSize in certain circumstances.

0 Votes 0 ·

Can you provide an example?

0 Votes 0 ·

If the parent row does not contain children (which is not unusual, I think), then it will not be deleted.


1 Vote 1 ·
Show more comments
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered MelissaMa-msft converted comment to answer

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your reply,

Basically i have child table as tbl_AutoSizeDetail and Parent table as tbl_AutoSize

I want to delete Child table first due to foreign key reference with same filter as require for parent table, Once i delete child table want to delete parent table with same filter. My Query instead of repeating filter in both deleting child table and parent table can we combined it with one set of filter and delete child first and after parent.

Hope that's clear

Thanks,
Shabbir

0 Votes 0 ·
pituach avatar image pituach ShabbirDaruwala-7855 ·

Good day,

There is no definition for "child table" and you are struggle to describe your tables using your personal description, which is make no sense and it is not useful.
strong text
We can get better understanding and sample scenario which we can text and provide tested solution, if you simply provide us DDL+DML to reproduce the scenario!

Please provide

  1. queries to create the tables and to insert some sample data, including relations between the tables (like Primary and foreign key).

  2. your expected result according to the sample data

  3. the version of the server which you use

Thanks



0 Votes 0 ·

My aim is i don't want to repeat below where clause in both delete function

WHERE 1=1
AND DepartmentId = @PAR_DepartmentId
AND SizePot = @PAR_SizePot
AND ProductSize = @PAR_ProductSize)

0 Votes 0 ·
OlafHelper-2800 avatar image
2 Votes"
OlafHelper-2800 answered

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);      
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @OlafHelper-2800

I assume that this is the right answer to the question. Can you convert the comment into answer, so the OP will be abel to mark it and close the thread?

Off-topic: why do you need the addition of "-2800" to your name :-) you can edit your user to be @OlafHelper for example

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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!)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @ShabbirDaruwala-7855,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ShabbirDaruwala-7855 avatar image
0 Votes"
ShabbirDaruwala-7855 answered

Great thanks everyone, I accept TOMZika as best solution.

Thanks everyone for your help.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.