-1

How I can delete data from several tables by one queries in SQL? I trying, but not working:

DELETE FROM "paginationsCount" AND
DELETE FROM "paginationsUrl" AND
DELETE FROM "taskStatus" AND
DELETE FROM "sessionId";

or

DELETE FROM "paginationsCount";
DELETE FROM "paginationsUrl";
DELETE FROM "taskStatus";
DELETE FROM "sessionId";
mxcdh
  • 805
  • 3
  • 10
  • 20
  • 2
    You need four DELETE statements if you want to delete from four tables. You can put them into a transaction if you want to make that atomic. –  Apr 27 '22 at 13:29
  • 1
    If those tables have foreign key relationships a FK definition with `on delete cascade` might do what you want. – clamp Apr 27 '22 at 13:34
  • Does this answer your question? [Postgresql delete multiple rows from multiple tables](https://stackoverflow.com/questions/33699480/postgresql-delete-multiple-rows-from-multiple-tables) – D-Shih Apr 27 '22 at 13:35

1 Answers1

0

We can run a delete in a CTE and use returning to use the id's of the deleted rows in a subsequent query, which can be SELECT, INSERT or DELETE.

create table table1(id1 int, id2 int);
create table table2(id2 int, id3 int);
create table table3(id3 int, id4 int);
create table table4(id4 int, def int);
insert into table1 values(11,21),(12,22);
insert into table2 values(21,31),(22,32);
insert into table3 values(31,41),(32,42);
insert into table4 values(41,1),(42,2);

select * from table1;
select * from table2;
select * from table3;
select * from table4;

id1 | id2
--: | --:
 11 |  21
 12 |  22

id2 | id3
--: | --:
 21 |  31
 22 |  32

id3 | id4
--: | --:
 31 |  41
 32 |  42

id4 | def
--: | --:
 41 |   1
 42 |   2

with 
del4 as (
delete from table4 where def = 1
returning id4),
del3 as (
delete from table3 where id4 in(select id4 from del4)
returning id3),
del2 as (
delete from table2 where id3 in(select id3 from del3)
returning id2)
delete from table1 where id2 in(select id2 from del2);



1 rows affected








select * from table1;
select * from table2;
select * from table3;
select * from table4;



id1 | id2
--: | --:
 12 |  22

id2 | id3
--: | --:
 22 |  32

id3 | id4
--: | --:
 32 |  42

id4 | def
--: | --:
 42 |   2





db<>fiddle here