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?
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)
);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments