sqldatabasepostgresqlrelational-databasesql-delete

Postgresql delete multiple rows from multiple tables

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

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