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?
Advertisement
Answer
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) );