1

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

Unihedron
  • 10,902
  • 13
  • 62
  • 72
Stan
  • 37
  • 5
  • 1
    What SQL engine/dialect are you using - MySQL, MS SQL, PostgreSQL? Does it support transactions, triggers, stored procedures etc.? – Artemix Sep 18 '12 at 12:17
  • i would prefer to avoid anithing like triggers or stored procedures – Stan Sep 18 '12 at 12:33
  • Since you don't want to use any SQL extensions the best way is to perform these three operations step-by-step. In this case the most intelligent way is to perform them in one transaction. – Artemix Sep 18 '12 at 12:46

3 Answers3

2

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

  • that is great, but unfortunately i dont know DBMS in advance :( In fact, i have to make it work irrespective on SQL engine/dialect – Stan Sep 18 '12 at 12:39
  • @Stan: making it DBMS independent simply means it will run equally slow/bad on all engines. You should consider DBMS specific solutions for anything that is more complex than `select * from foo`. –  Sep 18 '12 at 12:57
0

It is not possible to perform all of these operations in a single statement. You would have to do something like this:

UPDATE summary SET sum_number = somefunction(details.det_number)
FROM summary INNER JOIN details ON summary.sum_id = details.sum_id

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

DELETE FROM summary WHERE sum_number <= 0
Dan
  • 10,480
  • 23
  • 49
  • How can you be so sure there aren't other ways too? – askmish Sep 18 '12 at 13:12
  • Assuming OP is using the T-SQL dialect, there's no way (to my knowledge at least), to delete from two tables at once, using only one statement. Perhaps through some suitable use of views, this could in principle be achieved, but I think that kind of defies the point of doing it in an intelligent way. – Dan Sep 18 '12 at 19:28
0

I would use a trigger... then the database is responsible for the deletes.

Using an update trigger, once/if the Update is successfull if will fire the trigger which can do as much or as little as you need... i.e. it can do you're 2 deletes.

For an example have a read of this tutorial:

http://www.mysqltutorial.org/create-the-first-trigger-in-mysql.aspx this answer (http://stackoverflow.com/questions/6296313/mysql-trigger-after-update-only-if-row-has-changed) from stackoverflow also provides a good example.

Brian
  • 8,418
  • 2
  • 25
  • 32