How to delete rows from a different tables?

Question

I have a parent table with collection of IDs, how to transferred this collection to other tables in a block WHERE for deletion?
example:

select id from table_a <- it's collection of ids.
delete from table_b where table_b.ad_id in (COLLECTION_OF_IDS_FROM_PREVIOS_SQL_SCRIPT)
delete from table_c where table_c.ad_id in (COLLECTION_OF_IDS_FROM_PREVIOS_SQL_SCRIPT)
...

there is no possibility to cascade deletion

I find solution

delete from table_b where table_b.ad_id in (select id from table_a)
...

how to optimize this solution?

Also, this is an excellent case to use SQL transactions. BEGIN TRANSACTION, then issue the series of SQL commands necessary to accomplish the transfer, then COMMIT. (If something goes wrong, ROLLBACK.) When you do this, then, from the point-of-view of any other user, "either everything happened at once, or nothing did." (They will not see "intermediate states.") Use try..except logic in your programming to ensure that a ROLLBACK happens if a bug occurs. — Mike Robinson
– Mike Robinson, Commented Apr 15, 2020 at 19:02

Mike Robinson · Accepted Answer · 2020-04-15 19:11:35Z

1

After brief reflection, I want to bump my previous reply to an "answer."

A situation like this – in any SQL database – is an ideal case for SQL "transactions." A transaction is an atomic unit of work. First you BEGIN TRANSACTION, then you issue one or more SQL commands. When you finish, COMMIT. Or, if something goes wrong, ROLLBACK.

(Use try..except logic in your programming to ensure that ROLLBACK occurs if an exception is raised.)

Other users of the database system will either see that "all of the changes that you made occurred, instantaneously," when you COMMIT, or, that "none of them did." Other users will not see the database as being in any "intermediate states," even though you do.

answered Apr 15, 2020 at 19:11

Mike Robinson

9,0516 gold badges33 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2020-04-15 19:38:01Z

You can do that using a data modifying CTE:

with id_list as (
   select id 
   from table_a 
   where ...
), delete_b as (
   delete from table_b 
   where table_b.ad_id in (select * from id_list)
)
delete from table_c 
where table_c.ad_id in (select * from id_list);

This can also be used to delete rows from a hierarchy of tables that are linked with foreign keys (essentially a "manual" cascading delete).

with remove_root as (
   delete from root_table
   where ....
   returning pk_column
), delete_child_1 as (
  delete from child_one
  where root_id in (select * from remove_root)
  returning pk_column
), delete_child_2 as (
  delete from child_one
  where root_id in (select * from remove_root)
)
delete from grand_child
where child_one_id in (select * from delete_child_1);

Collectives™ on Stack Overflow

How to delete rows from a different tables?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related