CODEWITHSUNDEEP
sqlpostgresql
AbrA
AbrA

Reputation: 480

How to delete rows from a different tables?

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?

Upvotes: 0

Views: 44

Answers (2)

user330315
user330315

Reputation:

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

Upvotes: 0

Mike Robinson
Mike Robinson

Reputation: 8945

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.

Upvotes: 1

Related Questions