PostgreSQL从多个表中删除多行

Question

考虑2个或更多表：

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

我希望删除所有与名字“ Sam ”匹配的 用户 及其 订单 。在mysql中，我通常会做左连接。在此示例中，我们不知道userid。

查询的正确格式是什么？

Answer 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';

您也可以使用 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)
);