Postgresql delete multiple rows from multiple tables

Arranging proper cascading deletes is wise and is usually the correct solution to this.For certain special cases, there is another solution to this that can be relevant.

If you need to perform multiple deletes based on a common set of data you can use Common Table Expressions (CTE).

It's hard to come up with a simple example as the main use case for this can be covered by cascading deletes.

For the example we're going to delete all items in table A whose value is in the set of values we're deleting from table B. Usually these would be keys, but where they are not, then cascading delete can't be used.

To solve this you use CTEs

WITH Bdeletes AS (    DELETE from B where IsSomethingToDelete = true returning ValueThatRelatesToA)delete from A where RelatedValue in (select ValueThatRelatesToA from Bdeletes)

This example is deliberately simple because my point is not to argue over key mapping etc, but to show how two or more deletes can be performed off a shared dataset.This can be much more complex too, including update commands etc.

Here is a more complex example (from Darth Vader's personal database). In this case, we have a table that references an address table. We need to delete addresses from the address table if they are in his list of planets he's destroyed. We want to use this information to delete from the people table, but only if they were on-planet (or on his trophy-kill list)

with AddressesToDelete as (    select AddressId from Addresses a     join PlanetsDestroyed pd on pd.PlanetName = a.PlanetName),PeopleDeleted as (    delete from People     where AddressId in (select * from AddressesToDelete)    and OffPlanet = false     and TrophyKill = false    returning Id),PeopleMissed as (    update People     set AddressId=null, dead=(OffPlanet=false)    where AddressId in (select * from AddressesToDelete)    returning id)Delete from Addresses where AddressId in (select * from AddressesToDelete)

Now his database is up to date. No integrity failures due to Address deletion.Note that while we are returning data from the update and the first delete, it doesn't mean we have to use it. I'm uncertain whether you can put a delete in a CTE with no returned data (My SQL may also be wrong on the use of returning from an update - I've not been able to test run this as Darth V. was in a cranky mood.

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

DELETE FROM orders oUSING users uWHERE o.userid = u.id  and u.firstname = 'Sam';DELETE FROM users uWHERE 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));

Define userid as a foreign key to users (id) with cascading delete, e.g.:

create table users (    id int primary key,     firstname text,     lastname text);create table orders (    orderid int primary key,     userid int references users (id) on delete cascade,     orderdate date,     total numeric);delete from userswhere firstname = 'Sam';