Deleting from one table and updating another

Question

I have two tables with following columns:

SUMMARY(sum_id, sum_number) and DETAILS(det_id, det_number, sum_id)

I want to delete rows from table DETAILS with det_id in list of IDs, which can be done by:

DELETE FROM details WHERE det_id in (1,2,3...)

BUT

1. At the same time I need to update table SUMMARY if summary.sum_id=details.sum_id

   UPDATE summary SET sum_number-=somefunction(details.det_number) 
   WHERE summary.sum_id=details.sum_id
   

2. More over, afterwards it would be totally great to delete rows from SUMMARY table if sum_number<=0

How to do all this in an intelligent way?

What if i know, from the very beginning, both IDs: details.det_id (to delete) AND summary.sum_id which correspond to details.det_id

Answer 1

You did not specify a DBMS so I'm assuming PostgreSQL.

You can do this with a single statement using the new writeable CTE feature:

with deleted as (
   delete from details
   where det_id in (1,2,3...)
   returning details.*
),
new_summary as (
   update summary 
      set sum_number = some_function(deleted.det_number)
   from deleted
   where delete.sum_id = summary.sum_id
   returning summary.sum_id
) 
delete from summary 
where sum_number <= 0
and sum_id in (select sum_id from new_summary);

The in condition in the outer delete is not strictly necessary, but you may not have CTE definitions that you don't use, so the condition ensures that the new_summary CTE is actually used in the statement. Additionally it might improve performance a bit, because only the changed summary rows are checked (not all).