CODEWITHSUNDEEP
postgresql
Hozeis
Hozeis

Reputation: 1742

How to delete from table then delete what deleted rows referenced? (postgresql)

What I would like to do is delete rows from table 1 then delete from table 2 rows that the deleted rows from table 1 referenced with a foreign key. I want to know if I could do this without out having to delete my foreign key on table 2 and recreating it with on delete cascade.

I'm not great with SQL and attempted 3 different approches without any success.

1: Trying to delete from multiple tables at once

delete from table1 a, table2 b where a.table2_id = b.id and a.column < 0;

2: Delete with return

delete from table2 where id in
   (delete from table1 where column < 0 returning table2_id as id);

3: Create an array from select and use it to delete from both tables

DECLARE
   arr integer[] :=  array(select table2_id from table1 where column < 0);
BEGIN
   FOREACH m SLICE 1 IN ARRAY arr
   LOOP
      delete from table1 where table2_id = m;
      delete from table2 where id = m;
   END LOOP;
END

Upvotes: 1

Views: 976

Answers (2)

Riya Bansal
Riya Bansal

Reputation: 1301

I think you can do this if you can rearrange your delete.

Please try below updated function.

create table schema_name.table_name1 (id serial primary key, name text)
create table schema_name.table_name2(id bigint references schema_name.table_name1(id), mno bigint)

create or replace function schema_name.fn_test_f_k() returns void as $$
DECLARE
   c1 cursor for select id from schema_name.table_name1 where id = 1;
BEGIN
   for r IN c1
   LOOP
      delete from schema_name.table_name2 where id = r.id;
      delete from schema_name.table_name1 where id = r.id;
   END LOOP;
END
$$
LANGUAGE PLPGSQL SECURITY DEFINER;

select schema_name.fn_test_f_k();

Upvotes: 1

user330315
user330315

Reputation:

You can do this in a single statement using a writeable CTE

with t1_deleted as (
   delete from table1 where column < 0 
   returning table2_id as id
)
delete from table2 
where id in (select id from t1_deleted);

Upvotes: 2

Related Questions