Postgresql delete multiple rows from multiple tables

Question

Consider 2 or more tables:

users (id, firstname, lastname)
orders (orderid, userid, orderdate, total)

I wish to delete all users and their orders that match first name 'Sam'. In mysql, I usually do left join. In this example userid is unknown to us.

What is the correct format of the query?

Solution 1

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

Solution 2

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.

Solution 3

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 users
where firstname = 'Sam';

Comments

[fawzib]

thank you very much! this will be helpful for the future

[Klaws]

I find you lack of faith disturbing. If you don't need a RETURNING clause, RETURNING NULL will be an acceptable substitute...or you can completely omit it. Hint: it should be possible to write "DELETE FROM people USING AddressesToDelete WHERE AddressId = AddressesToDelete.id AND OffPlanet = false AND TrophyKill = false".

[Erik van Velzen]

The query PeopleDeleted does not run, correct?