Postgresql Delete Multiple Rows from Multiple Tables

Postgresql delete multiple rows from multiple tables

http://www.postgresql.org/docs/current/static/sql-delete.html

DELETE 
FROM orders o
USING users u
WHERE o.userid = u.id
  and u.firstname = 'Sam';

DELETE 
FROM users u
WHERE u.firstname = 'Sam';

You can also create the table with ON delete cascade

http://www.postgresql.org/docs/current/static/ddl-constraints.html

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

Delete records from multiple tables using Join with PostgreSQL

Instead, delete from one table at a time:

with t1 as (
      delete t1 
      where t1.col = 'something'
      returning *
     ),
     t3 as (
      delete t3
      where t3.t1_id in (select id from t1)
      returning *
     )
delete t2
where t2.id in (select t2_id from t3);

This is not exactly the same. The inner join requires matches among the tables. But I think this is your intention.

Delete rows from multiple tables in a single statement

The standard way to do that is to declare the foreign key as ON DELETE CASCADE. Doing so, if a user is deleted, all articles related to it will also be deleted.

CREATE TABLE articles (
  user_id int REFERENCES users (id) ON DELETE CASCADE);

Demo: db<>fiddle

If for some reason you're not able to change the table structure, consider using a DELETE ... RETURNING from users inside a CTE and then use the returned values in a outer DELETE on articles:

WITH j (id) AS (
  DELETE FROM users WHERE id = 1
  RETURNING id
)
DELETE FROM articles a USING j 
WHERE a.user_id = j.id;

Demo: db<>fiddle

PostgreSQL delete rows that outer join from multiple tables

Simply use not exists:

delete from tableA a
    where not exists (select 1 from tableB b where b.idB = a.idB) or
          not exists (select 1 from tableC c where c.idC = a.idC);

Delete multiple rows from a table with a single query when the row ids are not consecutive

You can use IN clause for this.
Sample query may look like below

DELETE FROM table WHERE id IN (1, 4, 6, 7)

---

Related Topics