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

Question

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

user330315 · Accepted Answer · 2016-12-13 12:26:46Z

2

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

answered Dec 13, 2016 at 12:26

user330315

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

Comments

Riya Bansal · Accepted Answer · 2016-12-13 12:56:56Z

1

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

edited Dec 13, 2016 at 12:56

answered Dec 13, 2016 at 12:19

Riya Bansal

1,3111 gold badge11 silver badges10 bronze badges

6 Comments

Riya Bansal Over a year ago

You can use delete queries without function just mention rows to be delete in where condition.

Riya Bansal Over a year ago

Its just you need to first delete rows from foreign key table(table2)

Hozeis Over a year ago

but i cant delete table2 first cuz table1 references it.

Riya Bansal Over a year ago

Its just you need to delete first from foreign key table before deleting rows from primary key table.

Hozeis Over a year ago

table1.table2_id = table2.id table one references table two.

|

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related