考虑2个或更多表:
users (id, firstname, lastname)
orders (orderid, userid, orderdate, total)
我希望删除所有users个和他们的orders个名字"Sam"匹配的名字.在mysql中,我通常使用左键连接.在这个例子中,用户ID对我们来说是未知的.
查询的正确格式是什么?
考虑2个或更多表:
users (id, firstname, lastname)
orders (orderid, userid, orderdate, total)
我希望删除所有users个和他们的orders个名字"Sam"匹配的名字.在mysql中,我通常使用左键连接.在这个例子中,用户ID对我们来说是未知的.
查询的正确格式是什么?
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)
);